SQL Window Functions
The analytics superpower most people skip: compute running totals, rankings, and row-to-row comparisons without collapsing your rows.
The idea
- What they do
- Compute across a set of rows related to the current row — without GROUP BY collapsing them.
- Syntax
func() OVER (PARTITION BY … ORDER BY …)- PARTITION BY
- Restart the calculation per group (like GROUP BY, but rows stay).
- ORDER BY (in OVER)
- Defines row order for running/ranking functions.
Ranking
- ROW_NUMBER()
- 1,2,3,4 — unique, no ties
- RANK()
- 1,2,2,4 — ties share, gaps after
- DENSE_RANK()
- 1,2,2,3 — ties share, no gaps
- NTILE(4)
- Split rows into 4 buckets (quartiles)
- Top-N per group
- Wrap in a subquery:
WHERE rn <= 3afterROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC)
Offsets & aggregates
- LAG / LEAD
LAG(sales) OVER (ORDER BY day)— previous/next row’s value (row-to-row diffs)- Running total
SUM(amt) OVER (ORDER BY day)- Moving average
AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)- First / last in window
FIRST_VALUE(x)·LAST_VALUE(x)- Share of total
x / SUM(x) OVER ()— percent of grand total