Window Functions

TL;DR: A window function computes over a set of rows related to the current row (the "window") without collapsing them, unlike GROUP BY which returns one row per group. With OVER (PARTITION BY ... ORDER BY ...) you get ranking within groups (ROW_NUMBER, RANK), running totals and moving averages (SUM/AVG over a frame), and neighbor comparisons (LAG/LEAD). They are the backbone of 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 into one row. A window function computes an aggregate or rank for each row over a related set of rows, keeping every row. The window is defined by OVER (PARTITION BY <group> ORDER BY <sort> <frame>): partition into groups, order within them, and optionally restrict to a frame of rows.

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 (ROWS vs RANGE) defines which rows are included, a common subtlety.
• Offset / neighbor: LAG/LEAD to 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.