SQL fluency: GROUP BY vs window functions (top-N per group, ranking, running totals)

A window computes across related rows and keeps every row; it runs after WHERE, so filter on it one query out.

Before you read: predict

ExamHall's results table holds two exams, three candidates each. The results page runs:

SELECT candidate_id, exam_id, score,
       max(score) OVER (PARTITION BY exam_id) AS best
FROM results
WHERE exam_id IN ('E1', 'E2');

What does the query return?

The 30-second version

GROUP BY collapses each group to one row. A window function, any function followed by OVER (...), computes across related rows and keeps every row. PARTITION BY picks the related rows; ORDER BY inside OVER gives ranks, running totals and "previous row". Windows run after WHERE, so filter their results in an outer query.

A window computes across related rows and keeps every row; it runs after WHERE, so filter on it one query out.

How it works

Step 1 of 14

GROUP BY collapses. It returns one row per group; its output holds only grouped columns and aggregates.

In the kitchen

the summary board: one line per category, "Latin: 12 couples, winner No. 41" → GROUP BY (step 1)

Analogy: A ballroom dancing competition's scrutineer (dancesport)

The scrutineer turns judges' marks into placings, category by category.

At the competition	In the database
the summary board: one line per category, "Latin: 12 couples, winner No. 41"	`GROUP BY` (step 1)
the full results: every couple's line kept, its placing in its own category beside it	a window keeps every row (steps 2–3)
placings come from the judges' marks, not from where a couple is listed on the sheet	`ORDER BY` inside `OVER` (step 4)
couples tied on marks share a placing; the official placings then skip the places the tie took, while the prize table, which counts prize tiers, skips none	`rank()` and `dense_rank()` (steps 6–7)
a couple scratched at registration never dances, so never gets a placing	`WHERE` runs first (step 8)
the door steward cannot admit "only the top three": placings exist only after judging, so the finals list is drawn from the results sheet	filter in an outer query (steps 9–10)
each couple's card shows its mark change since its previous round	`lag()` (step 13)

In real terms: compute the ranking over every row first, then choose rows from the ranked result.

Breaks down when: a scrutineer breaks a tie with the skating system's tie-break rules; rank() never breaks a tie, and row_number() breaks it in an unspecified order (steps 5–6).

Examples, run for real

4.1 ExamHall EXH-3830 → MR !4130: the leaderboard, from a Node loop to one query

ExamHall built each exam's top-3 leaderboard and every candidate's "score change since last attempt" in Node: it loaded every attempt, grouped them, sorted each group and compared neighbours. MR !4130 asks PostgreSQL instead: a rank() window per exam builds the leaderboard, and score - lag(score) OVER (PARTITION BY candidate_id ORDER BY attempted_at) the changes:

4.1 shipped (Node loop): rows sent to Node 40000 · leaderboard rows 799 · changes computed 33839
4.1 MR !4130 (rank() and lag() in SQL): rows sent to Node 799 for the leaderboard · same leaderboard yes · same 33839 changes yes
4.1 plan: 200 exams · plan nodes Subquery Scan on ranked > WindowAgg > Sort > Seq Scan on attempts

Both produce the same leaderboard and the same changes; only the rows that cross to Node differ (steps 2, 10, 13). The plan sorts by exam and score, then computes the ranks in one pass (step 14).

4.2 Chat: one table, grouped or windowed

The same seven messages, counted per room both ways:

4.2 GROUP BY room_id: 2 rows (r1 4, r2 3)
4.2 count(*) OVER (PARTITION BY room_id): 7 rows, each message with its room's count (#1 r1 4, #2 r2 3, #3 r1 4, ...)

GROUP BY answers "how many per room" in two rows; the window answers it on every message, so a page can show each message beside its room's count without a second query or a join (steps 1–3).

Try it

Answer first, then check. Nothing is saved.

Question 1 of 7

Exam E7's scores are 95, 91, 91, 87 and 80. ExamHall's results page runs:

SELECT candidate_id, score,
       rank()       OVER (ORDER BY score DESC) AS r,
       dense_rank() OVER (ORDER BY score DESC) AS d
FROM results
WHERE exam_id = 'E7';

What r and d does the candidate who scored 87 get?

Question 2 of 7

Exam E7's scores are 95, 91, 91, 87 and 80. A developer writes the top-3 query in one step:

SELECT candidate_id, score
FROM results
WHERE exam_id = 'E7'
  AND rank() OVER (ORDER BY score DESC) <= 3;

What does PostgreSQL return?

Question 3 of 7

A chat room's export numbers each message with how many the room had sent by its date. Room r1 has four messages: one on 3 March, two on 4 March and one on 5 March.

SELECT id, sent_on,
       count(*) OVER (ORDER BY sent_on) AS so_far
FROM messages
WHERE room_id = 'r1'
ORDER BY sent_on, id;

What so_far values does the export show, in order?

Question 4 of 7

A chat app's home screen has two slots under each room for its newest messages. Room r1 has four messages, two of which share the second-latest sent_at; room r2 has three. The screen needs exactly two messages per room, newest first, and either of r1's tied pair may take its second slot.

Which query from messages(id, room_id, sent_at) gives the screen what it needs?

Question 5 of 7

ExamHall's results page lists each exam's top 3 places from results(exam_id, candidate_id, score), with ties: a candidate tied with a top-3 score is listed, and an exam with fewer than three candidates lists them all.

Write the query.

Your answer (optional, stays on this page)

Question 6 of 7

ExamHall shows each candidate's score change since the last attempt. Candidate c-21 scored 60, then 72, then 70.

SELECT candidate_id, attempted_at, score,
       score - lag(score) OVER (PARTITION BY candidate_id ORDER BY attempted_at) AS change
FROM attempts
ORDER BY candidate_id, attempted_at;

What change values does c-21 get, in order?

Question 7 of 7

A chat app's room leaderboard counts messages sent per user. In room r1 the users sent: u1 40, u2 35, u3 22 and u4 10. In room r2: u5 90 and u3 50. To show u3 their own place in r1, the app runs:

SELECT user_id, rank() OVER (ORDER BY sent DESC) AS place
FROM room_stats
WHERE room_id = 'r1' AND user_id = 'u3';

What place does it return?

Go deeper

Pro tip

Name a window once with a WINDOW clause when several functions share it, so rank(), lag() and a running total cannot drift apart.
```
SELECT candidate_id, score, rank() OVER w AS place, score - lag(score) OVER w AS gap_to_next_above
FROM results WHERE exam_id = 'E7'
WINDOW w AS (ORDER BY score DESC);
```
When: two or more window functions in one query read the same partition and order (steps 3–4). Status: SELF (PostgreSQL's tutorial calls repeated OVER clauses "duplicative and error-prone"; not run as a graded item).

Source

quote-check.mjs checks every quotation below verbatim in the 18 page and in the 17 page. Valid as of 2026-10-13.

PostgreSQL 18 and 17, 3.5 Window Functions (the row's first source, tutorial-window), https://www.postgresql.org/docs/18/tutorial-window.html and https://www.postgresql.org/docs/17/tutorial-window.html (pg18-tutorial-window.txt, pg17-tutorial-window.txt): "window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would" (steps 1–2); "It is also possible to omit PARTITION BY, in which case there is a single partition containing all rows." (step 3); "The window ORDER BY does not even have to match the order in which the rows are output." (step 4); "produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any." (step 8); "They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they logically execute after the processing of those clauses." (step 9); "If there is a need to filter or group rows after the window calculations are performed, you can use a sub-select." (step 10); "this is duplicative and error-prone" (tip 1).
PostgreSQL 18 and 17, 4.2.8 Window Function Calls (the row's second source, syntax-window-functions, on the Value Expressions page), https://www.postgresql.org/docs/18/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS and https://www.postgresql.org/docs/17/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS (pg18-sql-expressions.txt, pg17-sql-expressions.txt): "this sets the frame to be all rows from the partition start up through the current row's last ORDER BY peer" (step 11); "In ROWS mode, CURRENT ROW simply means the current row." and "Without ORDER BY, this means all rows of the partition are included in the window frame, since all rows become peers of the current row." (step 12); "Window function calls are permitted only in the SELECT list and the ORDER BY clause of the query." (step 9).
PostgreSQL 18 and 17, 9.22 Window Functions (the function table; teaching only, it keys nothing because the row does not name it), https://www.postgresql.org/docs/18/functions-window.html and https://www.postgresql.org/docs/17/functions-window.html (pg18-functions-window.txt, pg17-functions-window.txt): "Returns the number of the current row within its partition, counting from 1." (step 5); "Returns the rank of the current row, with gaps" (step 6); "Returns the rank of the current row, without gaps; this function effectively counts peer groups." (step 7); "Returns value evaluated at the row that is offset rows before the current row within the partition" (step 13).

Read first: Reading EXPLAIN (ANALYZE, BUFFERS): scans, joins, estimates vs actuals

Checked against the tools and docs as of 2026-10-13.

Predict first