Window functions

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Predict the output

What does the query print?

SQL · PostgreSQL 17
CREATE TABLE scores (name text, pts int);
INSERT INTO scores VALUES ('a', 90), ('b', 90), ('c', 80);
SELECT name, rank() OVER w, dense_rank() OVER w
FROM scores WINDOW w AS (ORDER BY pts DESC)
ORDER BY name;

Options
Hint 1 of 1

What happens to the number after a tie?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A a|1|1, b|1|1, c|3|2

Correct. rank skips 2 after the tie; dense_rank doesn't.

rank() gives tied rows the same number and then skips ahead by the size of the tie. dense_rank() gives the next distinct value the next number, so c is 3 by rank and 2 by dense_rank.

Why not the others?
  • B a|1|1, b|2|2, c|3|3. That's row_number for both: ties aren't broken by rank functions.
  • C a|1|1, b|1|1, c|2|2. rank leaves a gap after a two-way tie.
  • D a|1|1, b|1|1, c|3|3. dense_rank never leaves gaps.
Result on PostgreSQL 17 (psql)
namerankdense_rank
a11
b11
c32

(3 rows)

30-second answer

A window function computes a value across a set of rows related to the current row without collapsing them like GROUP BY does. row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) numbers each customer's orders from newest; keep rows where it's 1. Others include rank, dense_rank, lag, lead and running totals with sum(...) OVER (ORDER BY ...).

Practise saying it · 35 s. No recording, just a timer

Explain window functions.

Say your answer out loud, then check which of these points you covered:

  • Value per row over a window
  • PARTITION BY and ORDER BY
  • Latest per group with row_number
  • rank vs dense_rank
  • Filter in an outer query

Deep dive

rank leaves gaps after ties (1, 1, 3); dense_rank doesn't (1, 1, 2); row_number breaks ties arbitrarily unless ORDER BY is unique.

PostgreSQL's DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC is a shorter latest-per-group.

For one customer at a time, ORDER BY created_at DESC LIMIT 1 with an index is fastest.

The default frame with ORDER BY is RANGE UNBOUNDED PRECEDING to CURRENT ROW, which treats ties as peers in running sums.

Latest order per customer Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
CREATE TABLE orders (id int, customer_id int, created_at date);
INSERT INTO orders VALUES
  (1, 1, '2026-10-01'), (2, 1, '2026-10-05'), (3, 2, '2026-10-03');
SELECT customer_id, id FROM (
  SELECT customer_id, id, row_number() OVER (
    PARTITION BY customer_id ORDER BY created_at DESC
  ) AS rn
  FROM orders
) t WHERE rn = 1 ORDER BY customer_id;

Common mistakes

  • Filtering on a window function in WHERE (it runs after WHERE; use a subquery or CTE).
  • Using row_number with a non-unique ORDER BY and expecting stable results.
  • Self-joins for 'previous row' instead of lag().

Senior take & follow-ups

Contrast with GROUP BY, give latest-per-group, and mention DISTINCT ON as the PostgreSQL shortcut.

Follow-up questions

Think of your answer first, then open the hint.

  1. Difference between rank and dense_rank?

    Show hint

    rank skips numbers after ties; dense_rank doesn't.

  2. How do you compute a 7-day moving average?

    Show hint

    avg(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Related questions

Suggest a fix if something here is wrong or out of date.