Window Functions
Window functions run calculations over a set of rows tied to the current row, without collapsing them the way GROUP BY does, so you can rank within groups, build running totals and moving averages, and compare a row against its neighbors (LAG/LEAD), all in a single pass. They anchor analytics SQL: top-N-per-group, sessionization, cohort analysis, and period-over-period. AI, ML, and GenAI interviews probe them because they are the single most-tested SQL skill and the clearest way to write analytical queries.
TL;DR: A window function calculates over a set of rows related to the current row (the "window") without collapsing them, unlike GROUP BY, which returns a single row per group. With
OVER (PARTITION BY ... ORDER BY ...)you get ranking within groups (ROW_NUMBER,RANK), running totals and moving averages (SUM/AVGover a frame), and neighbor comparisons (LAG/LEAD). They anchor analytics SQL, top-N-per-group, running calculations, cohort and period-over-period analysis, and the single most-tested SQL skill.
Compute across rows without collapsing them
GROUP BY aggregates and collapses each group down to one row. A window function computes an aggregate or rank for each row across a related set of rows, keeping every row intact. The window is specified by OVER (PARTITION BY <group> ORDER BY <sort> <frame>): split into groups, order within them, and optionally limit to a frame of rows.
RANK() OVER (PARTITION BY dept ORDER BY salary DESC)The main families
- Ranking:
ROW_NUMBER()(unique rank),RANK()/DENSE_RANK()(ties),NTILE(n)(quantile buckets). The standard tool for top-N-per-group (rank within a partition, keep rank <= N). - Running / moving aggregates:
SUM/AVG/COUNT ... OVER (ORDER BY ... ROWS BETWEEN ...)for running totals and moving averages. The frame (ROWSvsRANGE) defines which rows are included, a common subtlety. - Offset / neighbor:
LAG/LEADto compare a row to the previous/next (period-over-period growth, gaps between events).
What they unlock
- Top-N per group: rank within each partition and filter, far cleaner than correlated subqueries.
- Sessionization and gaps-and-islands: group consecutive events using ordering and offsets.
- Deduplication:
ROW_NUMBER()over a key keeps one row per duplicate group. - Cohort and period-over-period analysis: running totals, retention, and LAG-based growth.
A frequent trap: you cannot filter a window function in WHERE (it is computed after WHERE), so wrap it in a subquery/CTE or use QUALIFY where supported.
Why interviewers probe this
Window functions are the most-tested SQL skill because they express the analytical queries data work actually needs, and many candidates only know GROUP BY. A strong answer explains the compute-across-rows-without-collapsing idea, the three families (ranking, running, offset), and a concrete use like top-N-per-group, plus the WHERE-filtering gotcha. Fluency here signals you can do real analytics SQL, not just aggregates.
Common misconceptions
- "Window functions are just GROUP BY." GROUP BY collapses rows; window functions keep every row and compute over a related set.
- "You can filter a window result in WHERE." It is computed after WHERE; use a subquery/CTE or QUALIFY.
- "ROW_NUMBER and RANK are the same." ROW_NUMBER is always unique; RANK/DENSE_RANK handle ties differently.
- "The frame does not matter." ROWS vs RANGE and the default frame change running-total results, especially with ties.
Key takeaways
- Window functions compute over related rows without collapsing them, unlike GROUP BY.
- The families are ranking (ROW_NUMBER/RANK/NTILE), running/moving aggregates, and offset (LAG/LEAD).
- They power top-N-per-group, sessionization, deduplication, and period-over-period analysis.
- You cannot filter a window function in WHERE; wrap it or use QUALIFY.
Check yourself before an interviewer does. Answer from memory first.
A candidate writes WHERE row_number() OVER (...) = 1 and it errors. Why, and what's the fix?