SQLrow_numberrankdense_ranklaglead

Window Functions

A window function computes a value for each row from a set of related rows — rank, running total, previous value — without collapsing the rows the way GROUP BY does.

▶ InteractiveTry queriesInterview questionSee how this works internally →
Progress

OVER: the window

f(...) OVER (PARTITION BY p ORDER BY o) evaluates f for each row using the rows in its window: the rows with the same partition values, ordered by o, and — by default — from the start of the partition up to the current row and its peers. The row itself is kept; the function just adds a column. That is the whole difference from GROUP BY, which produces one row per group.

PARTITION BY is optional (one window for everything). ORDER BY is optional for ranking-free aggregates (sum(x) OVER () gives the grand total on every row). Several window functions in one query may share or differ in their windows; each is computed after WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT.

The functions

Ranking: row_number() numbers 1, 2, 3 with no ties; rank() gives ties the same number and skips (1, 2, 2, 4); dense_rank() gives ties the same number and does not skip (1, 2, 2, 3); ntile(n) splits into n buckets. Offset: lag(x, k) and lead(x, k) read the value k rows behind or ahead; first_value, last_value, nth_value read from the frame. Aggregates: any aggregate over the window — sum(x) OVER (ORDER BY date) is a running total, avg(x) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) is a 7-row moving average.

Frames and the LAST_VALUE surprise

When ORDER BY is present the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — everything up to the current row, including its peers. That is why sum() OVER (ORDER BY …) is a running total. It is also why last_value() with the default frame returns the *current* row: the frame ends there. Say ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you mean the whole partition.

ROWS counts physical rows; RANGE groups peers by value. Prefer ROWS unless you specifically want ties handled together; it is also cheaper.

Top-N per group, and where to filter

You cannot filter on a window function in the same query’s WHERE — it does not exist yet. Wrap the query in a subquery or CTE and filter there. row_number() OVER (PARTITION BY group ORDER BY score DESC) followed by WHERE rn <= N is the standard top-N-per-group and replaces a correlated subquery that would run once per group.

Cost: a window with PARTITION BY and ORDER BY needs the input sorted on (partition, order) columns — a Sort node, or an index that already provides that order. Several windows with different orderings mean several sorts.

Key points

  • Window functions add a column computed over related rows; the rows are kept.
  • row_number never ties; rank skips after ties; dense_rank does not.
  • Default frame with ORDER BY ends at the current row — running totals work, last_value does not.
  • Filter on a window result in an outer query; top-N-per-group is row_number + WHERE rn <= N.
  • A window costs a sort on (partition, order) unless an index provides it.

Window functions on real rows

Window functions on real rows
A window function computes a value per row from a set of related rows — and keeps every input row. That is the whole difference from GROUP BY.
SELECT u.country, o.id, o.total,
       row_number() OVER (PARTITION BY u.country ORDER BY o.total DESC) AS rn
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid' AND u.country IN ('DE','NL','FR')
ORDER BY u.country, rn
LIMIT 18
#countryidtotalrn
1DE38421548.611
2DE142920962.772
3DE221819742.883
4DE311919147.24
5DE210519029.665
6DE63618589.726
7DE25818243.837
8DE193517637.878
9DE101417110.839
10DE18816743.9610
11DE17616280.2611
12DE191416103.0112
13DE198215566.2513
14DE279115537.8814
15DE40715402.8515
16DE274614853.7616
17DE117714809.5117
18DE20114517.7418
What it does: Numbers rows 1, 2, 3 … inside each partition, in the window ORDER BY order. Always consecutive, always unique.
Watch out: Two orders with the same total still get different numbers — the tie is broken arbitrarily, so add a tiebreaker column if you need a stable result.

Try it in the playground

SELECT * FROM (
  SELECT u.country, o.id, o.total,
         row_number() OVER (PARTITION BY u.country ORDER BY o.total DESC) AS rn
  FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status = 'paid'
) t WHERE rn <= 3
ORDER BY country, rn;
Top 3 per group. →

When to use — and when not

Use it when
  • Rankings, running totals, moving averages, period-over-period deltas.
  • Top-N per group.
  • Per-row percentage of a group total.
Avoid it when
  • A simple per-group summary with no per-row detail needed — GROUP BY is cheaper and clearer.

Failure modes

  • last_value with the default frame.
  • Filtering on the window function in the same WHERE.
  • Nondeterministic row_number over ties, giving different results run to run.

See how this works internally →

Descend one layer: the same topic explained from the machinery up.

How does the executor evaluate a window — sort, partition, frame?
↓ Database Internals → Inside the Query Engine
← Subqueries, CTEs, EXISTS, UNION, CASE