Thanks for the great question.

Window functions are really useful, and can make some complicated queries much simpler.

However, its one area that I really wish SQL Server would catch up to Oracle and Db2.

For example, Db2 provides the ability to define a set of rows to apply the aggregate to, with code such as:

sum(X) over (partition by Y order by Z rows between 1 preceding and current row)

This would allow the SUM of X to be applied for each group of Y, ordered by Z - but only aggregating the previous row and the current row. Essentially creating a running total, but without CTEs or nested queries.

Just my 2 cents worth 🙂