When I first used window functions back in SQL Server 2005, I was in awe. I had always used inefficient self-joins to calculate things like running totals, and these really didn’t scale well with size-of-data. I quickly realized you could also use them for ranks and moving averages without those cumbersome self-joins, elaborate sub-queries, or #temp tables. Those all have their place, but window functions can make them feel old-school and dirty.
I’d also recommend learning more about the APPLY operator as well, as it can, depending on the circumstances, be even more effective than window functions (combined with common table expressions) for some of the use cases.