Window functions are what separate intermediate SQL writers from advanced ones. They let you compute values that span multiple rows — rankings, running totals, period-over-period comparisons — while keeping every row in the output.
What you'll learn
- The
OVER()clause and how window functions differ from aggregates PARTITION BY— reset the window per group (like GROUP BY, but rows stay intact)ORDER BYinsideOVER()— enables running totals and rankings- Ranking:
ROW_NUMBER,RANK,DENSE_RANK,NTILE - Navigation:
LAGandLEAD— read the previous or next row's value - Aggregation over a window:
SUM OVER,AVG OVER,MIN/MAX OVER - Frame clauses:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
What you'll practice on
The HR database (salary rankings, department running payroll) and the Store database (revenue trends, month-over-month growth). These are the exact query patterns you will see in data analyst and data engineering interviews.
Prerequisites
All previous courses. Window functions build on joins, aggregates, and CTEs.