Study notes · 11% of the exam

Window Functions

Window functions compute across related rows without collapsing them. Know how PARTITION BY, ORDER BY and the frame decide which rows each calculation sees, and where windows run in query processing.

Key points

1
OVER (PARTITION BY … ORDER BY …) keeps every row; GROUP BY would collapse them. An empty OVER () treats the whole result as one partition.
2
ROW_NUMBER is always unique (1, 2, 3, 4); RANK repeats on ties and skips (1, 2, 2, 4); DENSE_RANK repeats without gaps (1, 2, 2, 3). Add a unique tiebreaker when you need a deterministic winner.
3
With ORDER BY and no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: aggregates become running totals and tied peers share the same value.
4
ROWS counts physical rows, RANGE uses distances in the ORDER BY value (including intervals for dates), and GROUPS counts peer groups. Frames never cross partition boundaries.
5
Windows run after WHERE, GROUP BY and HAVING and before DISTINCT, ORDER BY and LIMIT. They can wrap aggregates (SUM(SUM(x)) OVER ()) but cannot appear in WHERE, GROUP BY or HAVING, so filter on them in an outer query.
6
Classic patterns: top-N per group (ROW_NUMBER/RANK in a subquery), gaps and islands (date minus ROW_NUMBER), sessionization (LAG flag plus running SUM), period-over-period change (LAG), and percent of total (x / SUM(x) OVER ()).
7
LAG/LEAD take an offset and a default; without a default they return NULL beyond the partition edge.

Common traps

LAST_VALUE(x) OVER (ORDER BY …) returns the current row because the default frame ends there. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING or FIRST_VALUE with the order reversed.
PostgreSQL has no QUALIFY, no IGNORE NULLS for LAG/LAST_VALUE, and no DISTINCT inside window aggregates (all verified on PostgreSQL 18).
Filtering RANK() <= N returns more than N rows when there are ties at the cutoff; ROW_NUMBER returns exactly N but picks arbitrarily among ties unless you add a tiebreaker.

Read the source

Test yourself on Window Functions

Ten questions, with the answer and explanation after each one.