Session 10 — Window functions

GROUP BY collapses rows; window functions keep every row while attaching a computed value to each one. You will master the OVER clause — PARTITION BY (grouping without collapsing), ORDER BY (the order the function sees) — and the four shapes: ranking (ROW_NUMBER, RANK, DENSE_RANK), neighbours (LAG/LEAD), running totals (SUM … OVER), and moving frames (ROWS BETWEEN).

Plan: Core ideas → worked windows → Quiz.

Time: about 70–80 minutes if you draw the partitions before reading the computed columns.

Session 10 — Window functions

The one-paragraph story

GROUP BY collapses many rows into one summary row — great for totals, but it loses the rows themselves. Window functions fix that: they compute an aggregate-like value while keeping every row. ROW_NUMBER() OVER (...), RANK(), LAG/LEAD, and SUM(x) OVER (...) attach a ranking, a running total, or a previous-row value to each row in place. The magic is in the OVER clause, which defines the window: which rows to group together (PARTITION BY), in what order (ORDER BY), and how big the frame is (ROWS BETWEEN ...). This session builds the mental model of windows — partitions, ordering, frames — and shows the four shapes that cover most reporting needs.

---

The shape of a window function

SELECT
    b.title,
    b.genre,
    ROW_NUMBER() OVER (PARTITION BY genre ORDER BY price DESC) AS rank_in_genre
FROM books b
ORDER BY genre, rank_in_genre;

Read the OVER (...) as: within each genre partition, order books by price descending, and compute the function over that ordering. The result keeps every row, and the window column tells each row where it sits:

| title | genre | rank_in_genre | |-------|-------|---------------| | The Glass Harbour | fiction | 1 | | Static Echoes | fiction | 2 | | Salt Roads | history | 1 | | North of Noon | history | 2 | | River Clock | mystery | 1 | | Prairie Circuit | mystery | 2 |

Window vs GROUP BY: GROUP BY genre would return two rows (one per genre); the window returns all six, each tagged with its position inside its genre. That is the entire difference in one sentence.

---

The OVER clause parts

• PARTITION BY col — split rows into groups (like GROUP BY, but without collapsing). No partition means one window over the whole result.
• ORDER BY col — the order inside each partition. Required for ranking functions and running totals; it defines "first", "previous", "next".
• Frame (ROWS BETWEEN ...) — how many rows the computation sees, once ordering exists. Defaults are usually right for ranking and running sums.

---

The four shapes you need

1. Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()

• ROW_NUMBER() — 1, 2, 3, … always distinct, ties broken arbitrarily.
• RANK() — ties share a number, then the count jumps: 1, 1, 3.
• DENSE_RANK() — ties share a number, no gaps: 1, 1, 2.

SELECT title, price,
       RANK()       OVER (ORDER BY price DESC) AS r,
       DENSE_RANK() OVER (ORDER BY price DESC) AS dr
FROM books
ORDER BY price DESC;

For prices 31.00, 27.50, 24.99, 22.00, 19.50, 18.00 (no ties): r = dr = 1..6. Add a second 31.00 book and the difference appears — RANK gives 1,1,3 while DENSE_RANK gives 1,1,2.

2. Accessing neighbours: LAG(col, n) and LEAD(col, n)

SELECT title, price,
       LAG(price)  OVER (ORDER BY published_year) AS prev_price,
       LEAD(price) OVER (ORDER BY published_year) AS next_price
FROM books
ORDER BY published_year;

LAG reaches the previous row in the window order, LEAD the next. The oldest book's prev_price is NULL (no predecessor).

3. Running totals: aggregate + OVER

SELECT sale_id, qty,
       SUM(qty) OVER (ORDER BY sale_id) AS running_total
FROM sales
ORDER BY sale_id;

The cumulative sum of qty up to each row: 2, 3, 6, 7, 9. The running total is an aggregate computed per row over a growing window — the frame defaults to "all rows up to the current one" when there is an ORDER BY.

4. Moving aggregates: ROWS BETWEEN

SELECT sale_id, qty,
       AVG(qty) OVER (ORDER BY sale_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS ma2
FROM sales
ORDER BY sale_id;

A 2-row moving average: each row's value averages with the previous row's. Frames give you full control over the window's width.

---

Partitioned running totals

SELECT b.genre, s.qty,
       SUM(s.qty) OVER (PARTITION BY b.genre ORDER BY s.sale_id) AS genre_so_far
FROM sales s JOIN books b ON b.book_id = s.book_id
ORDER BY b.genre, s.sale_id;

The running total restarts for each genre — partition (group) plus order (cumulative). Partition + order is the combination behind nearly every "running total within category" report.

---

Pitfalls to watch for

• Window functions keep every row — if you expected one row per group, you wanted GROUP BY, not a window.
• ORDER BY inside OVER is the window's order, not the query's output order. Write a final ORDER BY for the result too.
• RANK gaps vs DENSE_RANK no-gaps — choose based on whether "3rd place" after a tie should skip 2.
• LAG/LEAD return NULL at the edges — that is normal, handle it with COALESCE if needed.

Session 10 — Worked examples

The bookshop data:

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

sales: (100, 10, 2), (101, 12, 1), (102, 10, 3), (103, 14, 1), (104, 13, 2) — (sale_id, book_id, qty)

Example A — Ranking inside a partition

SELECT b.title, b.genre, b.price,
       ROW_NUMBER() OVER (PARTITION BY genre ORDER BY price DESC) AS pos
FROM books b
ORDER BY genre, pos;

Windows: fiction (2 rows), history (2), mystery (2). Within each, order by price descending and number 1..n:

| title | genre | price | pos | |-------|-------|-------|-----| | The Glass Harbour | fiction | 24.99 | 1 | | Static Echoes | fiction | 19.50 | 2 | | Salt Roads | history | 31.00 | 1 | | North of Noon | history | 27.50 | 2 | | River Clock | mystery | 22.00 | 1 | | Prairie Circuit | mystery | 18.00 | 2 |

Trace discipline: draw the partitions first (group rows by genre), sort inside each, then number them — the window column is written last, row by row.

---

Example B — RANK vs DENSE_RANK on a tie

Add a second book priced 31.00 (book 16) so history has 31.00, 31.00, 27.50:

SELECT title, price,
       RANK()       OVER (ORDER BY price DESC) AS r,
       DENSE_RANK() OVER (ORDER BY price DESC) AS dr
FROM books
ORDER BY r;

| title | price | r | dr | |-------|-------|---|---| | Salt Roads | 31.00 | 1 | 1 | | Book 16 | 31.00 | 1 | 1 | | North of Noon | 27.50 | 3 | 2 | | The Glass Harbour | 24.99 | 4 | 3 |

RANK skips from 1 to 3 after the tie (three books claim ranks 1–2); DENSE_RANK continues at 2. Both put the tied books at 1.

---

Example C — LAG and LEAD

SELECT b.title, b.published_year,
       LAG(b.title)  OVER (ORDER BY published_year) AS previous_title
FROM books b
ORDER BY published_year;

| title | published_year | previous_title | |-------|----------------|----------------| | North of Noon | 2017 | NULL | | River Clock | 2018 | North of Noon | | The Glass Harbour | 2019 | River Clock | | Salt Roads | 2020 | The Glass Harbour | | Static Echoes | 2021 | Salt Roads | | Prairie Circuit | 2022 | Static Echoes |

LAG reads the row before in the window order; the first row has none, hence NULL. LEAD is the mirror.

---

Example D — Running total

SELECT sale_id, qty,
       SUM(qty) OVER (ORDER BY sale_id) AS running_total
FROM sales
ORDER BY sale_id;

The frame is "up to and including the current row": 2 → 2+1=3 → 3+3=6 → 6+1=7 → 7+2=9.

| sale_id | qty | running_total | |---------|-----|---------------| | 100 | 2 | 2 | | 101 | 1 | 3 | | 102 | 3 | 6 | | 103 | 1 | 7 | | 104 | 2 | 9 |

---

Example E — Partitioned running total

SELECT b.genre, s.qty,
       SUM(s.qty) OVER (PARTITION BY b.genre ORDER BY s.sale_id) AS genre_so_far
FROM sales s JOIN books b ON b.book_id = s.book_id
ORDER BY b.genre, s.sale_id;

Sales by genre: fiction has sales 100 (qty 2) and 102 (qty 3); mystery has 101 (1); history has 103 (1), 104 (2).

| genre | qty | genre_so_far | |-------|-----|--------------| | fiction | 2 | 2 | | fiction | 3 | 5 | | history | 1 | 1 | | history | 2 | 3 | | mystery | 1 | 1 |

The running total restarts at each genre partition — that is PARTITION BY genre + ORDER BY sale_id working together.

---

Example F — Moving average frame

SELECT sale_id, qty,
       AVG(qty) OVER (ORDER BY sale_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS ma2
FROM sales
ORDER BY sale_id;

Row 100: only itself → 2.0. Row 101: (2+1)/2 = 1.5. Row 102: (1+3)/2 = 2.0. Row 103: (3+1)/2 = 2.0. Row 104: (1+2)/2 = 1.5. The frame limits the window to two rows instead of "everything up to now".