Window functions: ranking, offsets and frames

row_number, rank and dense_rank, lag and lead, and the default frame that makes running totals and last_value give surprising answers.

After this lesson you can

Choose between row_number, rank and dense_rank for a top-N question
Compare each row with the previous one using lag
Write running and moving aggregates that stay correct when the ORDER BY has ties
Explain the default window frame and when to replace it

A window function computes a value for each row from a set of related rows (its window) without collapsing them the way GROUP BY does. OVER (PARTITION BY ... ORDER BY ...) defines the window: partitions are independent groups, and the order says which rows come before which. Window functions run after WHERE, GROUP BY and HAVING, so you cannot filter on them in the same WHERE; wrap the query and filter outside.

Ranking: three ways to number ties

row_number() — 1, 2, 3, ... always distinct. With ties, which row gets which number is arbitrary unless the ORDER BY breaks the tie.
rank() — ties share a number and leave a gap: 1, 1, 3.
dense_rank() — ties share a number, no gap: 1, 1, 2.

"Top 2 per team" means different things with each: row_number() <= 2 returns exactly two players; dense_rank() <= 2 returns everyone with one of the two best scores.

Given — already loaded, nothing to run heresql

1CREATE TABLE scores (id int PRIMARY KEY, player text NOT NULL, team text NOT NULL, points int NOT NULL);2INSERT INTO scores VALUES3  (1, 'Nino', 'red', 30), (2, 'Ana', 'red', 50), (3, 'Luka', 'red', 50),4  (4, 'Mari', 'blue', 20), (5, 'Gio', 'blue', 40), (6, 'Ia', 'red', 10);

Three numbering functions over the same tiepostgres-16

1SELECT team, player, points,2       row_number() OVER (PARTITION BY team ORDER BY points DESC, player) AS row_num,3       rank()       OVER (PARTITION BY team ORDER BY points DESC)         AS rnk,4       dense_rank() OVER (PARTITION BY team ORDER BY points DESC)         AS dense_rnk5FROM scores6ORDER BY team, points DESC, player;

What to look for

Offsets: lag and lead

lag(x) is the value of x in the previous row of the window, lead(x) in the next; both return NULL off the edge, unless you pass a default: lag(x, 1, 0). They answer "change since last time" questions without a self-join.

Frames: the part that bites

Aggregates used as window functions (sum, avg...) and the value functions first_value, last_value and nth_value work on a frame, a subset of the window. With an ORDER BY and no frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and in RANGE mode "current row" includes all its peers: rows with equal ORDER BY values. Two consequences:

A running total ordered by a column with ties gives every tied row the same total, the one after all of them.
last_value(x) returns the current row's (last peer's) value, not the partition's last, because the frame ends at the current row.

Given — already loaded, nothing to run heresql

1CREATE TABLE ledger (id int PRIMARY KEY, day date NOT NULL, amount int NOT NULL);2INSERT INTO ledger VALUES3  (1, '2024-01-01', 100), (2, '2024-01-02', -30), (3, '2024-01-02', 50), (4, '2024-01-03', -20);

The default frame, peers and last_valuepostgres-16

1SELECT id, day::text AS day, amount,2       sum(amount) OVER (ORDER BY day)     AS total_by_day,3       sum(amount) OVER (ORDER BY day, id) AS total_by_day_id,4       last_value(amount) OVER (ORDER BY day, id) AS last_default,5       last_value(amount) OVER (ORDER BY day, id6         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_whole,7       amount - lag(amount, 1, 0) OVER (ORDER BY day, id) AS change8FROM ledger9ORDER BY day, id;

What to look for

The fixes: make the order unique (ORDER BY day, id), or state the frame explicitly. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a 3-row moving average; ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING makes last_value see the whole partition.

Given — already loaded, nothing to run heresql

1CREATE TABLE visits (d int PRIMARY KEY, v int NOT NULL);2INSERT INTO visits VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50);

A 3-row moving average and share of the totalpostgres-16

1SELECT d, v,2       avg(v) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3,3       round(100.0 * v / sum(v) OVER (), 1) AS pct_of_total4FROM visits5ORDER BY d;

What to look for

sum(v) OVER () with an empty window is the grand total on every row, which makes "percentage of total" a one-liner.

Common mistakes

Running totals ordered by a date when several rows share a date; the tied rows all show the end-of-day figure.
last_value without an explicit frame, as shown above.
row_number() over an order with ties, used to pick "the latest" row; the winner can change between runs.
Filtering WHERE rank() OVER (...) <= 3; window functions are not allowed in WHERE.

What interviewers ask

rank versus dense_rank versus row_number? Gaps after ties, no gaps, always unique; give a top-N example where they differ.
Why does my running sum repeat the same value on some rows? The default RANGE frame includes peers; add a tie-breaker or use ROWS.
Second-highest salary per department? dense_rank() in a subquery partitioned by department, then filter = 2 outside.

Exercise · Pro

A three-reading moving average per sensor

Exercises are free in the first 3 lessons of every course. From here on they are part of Pro and Sprint; the lesson and its runnable examples stay open.

Upgrade

Previous Next