Window Functions

A window function computes over a set of related rows and attaches the answer to every row, collapsing nothing (N rows in, N rows out); almost every surprise follows from when it runs in the query pipeline.

When it runs

Logical order: FROM/JOIN → WHERE → GROUP BY → HAVING → window functions → SELECT → DISTINCT → ORDER BY → LIMIT.
Runs after WHERE/GROUP BY/HAVING, before DISTINCT/ORDER BY/LIMIT.
So you cannot write WHERE rn = 1 or HAVING rank <= 3: the value does not exist yet. Compute in a CTE, filter outside (or QUALIFY in Snowflake/BigQuery/Databricks/DuckDB).
LIMIT never makes a window cheaper (step 9, runs last). A WHERE upstream changes every rank and running total, because the window only sees surviving rows.

Anatomy of `OVER (...)` and its defaults

Clause	Default when omitted	Meaning
`PARTITION BY`	one partition = all rows (never an error)	`GROUP BY` that does not collapse
`ORDER BY` (inside `OVER`)	no order, all rows are peers	adding it turns an aggregate into a running one
frame	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	in `RANGE`, `CURRENT ROW` = me and all my peers

A peer is a row the window ORDER BY cannot distinguish from the current one. A total order has no ties.
Windows are legal only in the SELECT list and the query’s final ORDER BY; they cannot be nested (layer CTEs).

Ranking family (all ignore the frame)

Function	Ties get	After a tie
`ROW_NUMBER()`	different numbers (arbitrary)	continues; always `1..n`
`RANK()`	same number	skips by tie width (gaps)
`DENSE_RANK()`	same number	+1, no skip

PERCENT_RANK() = (rank-1)/(n-1); 0 for a single-row partition. CUME_DIST() = (preceding or peer)/n. NTILE(k) splits into k buckets, larger first; trailing buckets empty when n < k.
ROW_NUMBER over a non-total order is non-deterministic (VACUUM, stats, parallel workers reshuffle ties). Fix: append a unique tiebreaker, e.g. ORDER BY points DESC, player.

`LAG` / `LEAD` — period over period

LAG(expr, offset=1, default=NULL); reads an earlier row. LEAD reads a later one. Frame-insensitive.
First row’s LAG is NULL, not 0 — leave it; LAG(x,1,0) invents a fake delta.
LAG(x, 12) is 12 rows back, not 12 months: a missing month shifts everything. Densify with a calendar spine or use a value-based RANGE.
Guard division: / NULLIF(LAG(x),0), and multiply by 100.0 first (integer division truncates in Postgres/SQLite/SQL Server/Db2).

`ROWS` vs `RANGE` vs `GROUPS`

Bounds count different units; they only disagree when the ORDER BY key has duplicates.

ROWS = physical rows. RANGE = values of the key (CURRENT ROW = me + all peers). GROUPS = distinct key values.
Default frame (with ORDER BY, no frame clause) is RANGE ... CURRENT ROW, so a running total over a non-unique key jumps ahead of itself, summing the whole peer group.
Running-total rule: write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or make the key unique. ROWS alone still leaves intermediate values arbitrary inside a tie; for charts do both.
Bounds: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING. Exclusion: EXCLUDE CURRENT ROW / GROUP / TIES / NO OTHERS (Postgres 11+, SQLite 3.28+).
Moving “3 days”: ROWS counts rows (wrong across gaps); RANGE BETWEEN INTERVAL '2 days' PRECEDING counts calendar days. State whether an absent day means “no data” (skip) or “zero” (densify).

`FIRST_VALUE` / `LAST_VALUE` / `NTH_VALUE` (frame-sensitive)

Default frame ends at CURRENT ROW, so LAST_VALUE silently returns the current row’s own value. Fix: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or flip the sort and use FIRST_VALUE.
FIRST_VALUE works only because the default frame starts unbounded. NTH_VALUE(x, n) returns NULL early in the partition without an explicit frame.

Patterns, one line each

Pattern	Recipe
Running total	`SUM(x) OVER (ORDER BY k ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`
Top-N per group	`ROW_NUMBER() OVER (PARTITION BY g ORDER BY k DESC, uniq)`, keep `rn <= N`
De-duplicate	`ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY winner_rule, uniq)`, keep `= 1`
Find duplicates	`COUNT(*) OVER (PARTITION BY natural_key) > 1`
Sessionize / islands	`LAG` gap → flag → `SUM(flag) OVER (... ROWS UNBOUNDED PRECEDING)` → `GROUP BY`
Consecutive-day streak	group by `d - ROW_NUMBER() OVER (ORDER BY d)`, over distinct `d`
Percent of total after `GROUP BY`	`SUM(x) / SUM(SUM(x)) OVER ()` (double aggregate, not a typo)

“Top 2” is ambiguous: rn <= 2 (exactly 2), rk <= 2 (≥2, keeps ties), drk <= 2 (top 2 distinct values). Wrong choice can fan out a downstream join.
COUNT(DISTINCT x) OVER () does not exist in Postgres — pre-aggregate to the right grain first.

Performance: count the sorts

Each distinct (PARTITION BY, ORDER BY) spec costs one Sort, unless an index supplies the order or a prior compatible window already sorted the rows (a shorter key that is a prefix reuses the sort).
Collapse windows onto as few specs as possible; declare them once in a WINDOW clause. A stray DESC or extra tiebreaker doubles the sorts.
UNBOUNDED PRECEDING .. CURRENT ROW is an O(1)-per-row accumulator. SUM/COUNT/AVG are invertible over sliding frames; MIN/MAX are not, so a shrinking frame re-scans. Only WHERE (not LIMIT) reduces window work.

When it runs

Anatomy of OVER (...) and its defaults