Aggregates with GROUP BY collapse many rows into one. Window functions do something different: they calculate across a set of related rows while keeping every row in the result. That makes them the go-to tool for ranking, running totals, and comparing a row with the ones around it.
You'll learn the OVER clause and how PARTITION BY and ORDER BY define the window; ranking with ROW_NUMBER, RANK, and DENSE_RANK; running totals and moving averages using window frames; and looking at neighboring rows with LAG and LEAD.
Work through the lessons in order, then continue on to Data Modification.
A visual introduction to what a window function is and how OVER and PARTITION BY define the group of rows it looks at.
A hands-on lesson in SQL Server on numbering rows within groups using ROW_NUMBER() with PARTITION BY.
Explains how the three ranking functions differ, especially in how they handle ties.
Shows how window frames control which rows are included, with running totals and moving averages as examples.
Applies several window functions to data-analysis tasks, including comparing a row with the previous or next one.
A focused look at LAG and LEAD for accessing values from the previous and following rows.