Window Functions

Everything that filters or groups so far in the SQL for Beginners series removes rows on the way: WHERE drops them, GROUP BY collapses many into one. Chapter 16 of this 24-chapter series adds the construct that computes across rows while leaving every row in place — the window function, the engine behind rankings, running totals, and “how does this row compare with its group?” reports.

Reporting without collapsing rows

Suppose each order should show how many orders its customer has placed. With GROUP BY you would aggregate first, then join the answer back to the rows you started with. A window function does it in one pass: the function is evaluated over a window — the rows related to the current row by an optional PARTITION BY — and a new column appears without anything being discarded:

SELECT c.first_name, o.id,
       COUNT(*) OVER (PARTITION BY o.customer_id) AS their_orders
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY c.id, o.id;

Expected output:

first_name | id | their_orders
Aarav | 1 | 2
Aarav | 2 | 2
Priya | 3 | 1
Lena | 4 | 1
Wei | 5 | 1
Amara | 6 | 1

Two observations. Aarav keeps both of his order rows, each stamped with the same 2, because PARTITION BY gives every row of his a private window. And the six rows are still six rows — nothing was grouped away. Support is wide but not universal: SQLite has had windows since 3.25 (2018), PostgreSQL since 8.4, MySQL only from 8.0 — MySQL 5.7 has no window functions at all.

ROW_NUMBER, RANK and DENSE_RANK

Ranking is the most-used family. All three take an OVER (ORDER BY ...) that says how to line the rows up; they differ only in what they do with ties — and the order items contain two natural ties (899 twice, 499 twice), so all three appear at once:

SELECT id, CAST(unit_price AS INTEGER) AS unit_price,
       ROW_NUMBER() OVER (ORDER BY unit_price DESC, id) AS rn,
       RANK() OVER (ORDER BY unit_price DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY unit_price DESC) AS dr
FROM order_items;

Expected output:

id | unit_price | rn | rk | dr
3 | 899 | 1 | 1 | 1
8 | 899 | 2 | 1 | 1
4 | 749 | 3 | 3 | 2
7 | 649 | 4 | 4 | 3
1 | 499 | 5 | 5 | 4
5 | 499 | 6 | 5 | 4
6 | 349 | 7 | 7 | 5
2 | 249 | 8 | 8 | 6

Read the two tied rows at 899: rn hands out 1 and 2 (it never repeats), rk gives both rows 1 and then jumps to 3 (a gap, exactly like a race with shared podium places), and dr gives both rows 1 and continues at 2 (dense, no gap). The wider toolkit:

PARTITION BY: one window per group

PARTITION BY splits the rows into independent windows — the GROUP BY of window functions, except it changes no rows. Number each customer’s orders chronologically and the counter restarts for every customer:

SELECT c.first_name, o.id, o.order_date,
       ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS seq
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY c.id, o.order_date;

Expected output:

first_name | id | order_date | seq
Aarav | 1 | 2026-01-12 | 1
Aarav | 2 | 2026-01-20 | 2
Priya | 3 | 2026-02-03 | 1
Lena | 4 | 2026-02-11 | 1
Wei | 5 | 2026-02-25 | 1
Amara | 6 | 2026-03-02 | 1

Every partition restarts at 1, so “first order” becomes a single question answered for every customer who has ordered at once — the shape of a “latest invoice per client” report. Note that the ORDER BY inside the OVER() defines the ranking, while the one at the end of the statement only decides how the printed rows are sorted.

Aggregates that keep their rows

Any aggregate becomes a window function by giving it an OVER clause. Sum an order’s lines, then — without losing the per-order row — add the total for that order’s status. Summing a sum over the whole table gives the shop total too:

SELECT o.id, o.status,
       CAST(SUM(oi.quantity * oi.unit_price) AS INTEGER) AS order_total,
       CAST(SUM(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY o.status) AS INTEGER) AS status_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.status
ORDER BY o.id;

Expected output:

id | status | order_total | status_total
1 | paid | 997 | 3444
2 | shipped | 899 | 2246
3 | cancelled | 749 | 749
4 | shipped | 1347 | 2246
5 | paid | 649 | 3444
6 | paid | 1798 | 3444

The nested SUM(SUM(...)) reads oddly but follows the grammar: the inner sum runs per group, the outer runs per window over those results. Drop PARTITION BY (use OVER ()) and every row would receive the shop’s grand total, 6439 — the number chapter 13 calculated with a scalar subquery.

Frames: running totals and more

Which rows does an aggregate window see? The frame answers that, and for a running total you want everything from the start up to the current row:

WITH order_totals AS (
    SELECT order_id, CAST(SUM(quantity * unit_price) AS INTEGER) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT o.id, ot.total,
       SUM(ot.total) OVER (
           ORDER BY o.id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders o
JOIN order_totals ot ON ot.order_id = o.id
ORDER BY o.id;

Expected output:

id | total | running_total
1 | 997 | 997
2 | 899 | 1896
3 | 749 | 2645
4 | 1347 | 3992
5 | 649 | 4641
6 | 1798 | 6439

Each row adds its total to everything before it, ending at 6439 — the whole shop’s revenue, reached row by row. Two frame details save hours: the default frame is RANGE UNBOUNDED PRECEDING, which sweeps in every peer (tie) of the current row, so use ROWS when you want exact row-by-row arithmetic; and swap the endpoints (CURRENT ROW AND UNBOUNDED FOLLOWING) for a “what remains” total.

Peeking at neighbours: LAG and LEAD

LAG and LEAD reach one row backwards or forwards in the window’s order — comparisons that used to need a self-join. Run this straight from the shell to see each order beside its predecessor’s status:

sqlite3 shop.db <<'SQL'
.mode list
.separator ' | '
SELECT id, order_date, status,
       COALESCE(LAG(status) OVER (ORDER BY order_date), 'none yet') AS prev_status
FROM orders;
SQL

Expected output:

1 | 2026-01-12 | paid | none yet
2 | 2026-01-20 | shipped | paid
3 | 2026-02-03 | cancelled | shipped
4 | 2026-02-11 | shipped | cancelled
5 | 2026-02-25 | paid | shipped
6 | 2026-03-02 | paid | paid

The first row has no predecessor, so LAG returns NULL and COALESCE labels it; the last row would get NULL from LEAD for the mirror-image reason. FIRST_VALUE finishes the set — FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) stamps every row with its category’s priciest title, which is how “flag the best seller in each aisle” reports are built.

Common mistakes

• Writing a bare aggregate where an OVER was meant — SUM(x) collapses the query to one row, SUM(x) OVER () annotates every row; the missing parentheses-with-a-clause is the whole difference.
• Expecting ROW_NUMBER() to break ties fairly — ties are resolved arbitrarily, so the winner of a tied ranking can change between runs; add a deterministic second sort key such as id.
• Trusting the default frame for running totals — RANGE includes all peer rows with equal sort keys; write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for exact arithmetic.
• Filtering a window result in WHERE — WHERE runs before windows are computed, so it cannot see them; filter in an outer query or a CTE (MySQL’s QUALIFY does not exist in SQLite or PostgreSQL).
• Confusing the two ORDER BYs — the one inside OVER() defines the window’s order, the final one only sorts the printed result.
• Assuming the engine supports windows at all — SQLite needs 3.25+, MySQL needs 8.0+, and older servers reject the syntax outright.

Practice checklist

1. Rank all six products by price three ways (ROW_NUMBER, RANK, DENSE_RANK) and explain any column where the numbers differ.
2. Count each customer’s orders with a window, then redo it with GROUP BY, and list exactly what the two answers cannot do that the other can.
3. Build the running-total query, then change ROWS to RANGE and look for a difference — there is none until two totals tie, so create a tie and look again.
4. Use LEAD(order_date) to show the gap to each customer’s next order, and handle the final row’s NULL with COALESCE.
5. Reproduce the per-status totals with a join to a grouped subquery instead of a window, and compare how many lines each version takes.

What's next?

Windows solved ordering and arithmetic across rows; the values themselves still came ready-made. Next, take strings apart and search them — concatenation, wildcards and pattern matching in String Functions and Pattern Matching.