James McGillivray shows off some of the power of window functions:
Windowing functions are an underused feature in SQL Server, with myriad uses. The most common problems we solve are running totals, seeing group totals on the same line as individual lines from the group (allowing calculations like subtotal %). I don’t know if there are better solutions, but before Windowing Functions, I used to solve these kind of problems with self joins, or nested queries. The performance of Windowing Functions is significantly better than this approach, and that alone has made my life considerably better.
The more I learn about Windowing Functions, the more often I see use cases where they are useful.
I love talking about, and teaching people to use, Windowing functions to make their lives better, so I’m quite excited to be able to use them as the topic for today’s post.
Specifically, James looks at groups of groups problems and aggregates of aggregates problems.