I remember using window functions to create complex data outputs for data transformation pipelines. I didn't even know what they were but postgres just started supporting them and it looked like the right solution at the time.

I don't find a whole of use for them now but, I believe simply being aware they exist and an idea of what they do is easy enough to research and apply to any project you are working on. This is from a analyst and dev perspective.