Lesson 6 of 10 · 20m

Window functions

Ranking, running totals and row-to-row comparisons without collapsing rows — plus the default frame that makes LAST_VALUE return the current row, and why a window function cannot go in WHERE.

GROUP BY collapses rows. A window function does the same arithmetic and keeps every row, adding the result as another column. That one difference is why so many queries that need a subquery in GROUP BY form need nothing at all in window form.

Both engines support them. MySQL from 8.0 — on 5.7 none of this exists.

SELECT id, customer_id, placed_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS recency
FROM "order";

Read OVER (...) as “looking at this set of rows, in this order”:

PARTITION BY — restart the calculation per group. Like GROUP BY, but without collapsing.
ORDER BY — the order within the partition. For ranking it decides the ranks; for aggregates it decides what “so far” means.

Omit PARTITION BY and the whole result is one partition.

Ranking, and the three functions people mix up

Given scores 90, 90, 80:

Function	Result	Behaviour on ties
`ROW_NUMBER()`	1, 2, 3	arbitrary but distinct — ties are broken by nothing in particular
`RANK()`	1, 1, 3	ties share a rank, then the next rank skips
`DENSE_RANK()`	1, 1, 2	ties share a rank, no gap

Choose deliberately. A leaderboard where two people tie for first and the next person is “third” wants RANK. A numbered list wants ROW_NUMBER. And because ROW_NUMBER’s tie-breaking is arbitrary, add a unique column to the ORDER BY when the result must be stable — the same rule as pagination in lesson 1.

Top N per group

This is the query that makes window functions worth learning.

-- The most recent order per customer
SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC, id DESC) AS rn
  FROM "order" o
) ranked
WHERE rn = 1;

The subquery is not optional, and the reason is the clause order from lesson 3: window functions are computed after WHERE, at the same stage as SELECT. So WHERE rn = 1 cannot see rn, and WHERE ROW_NUMBER() OVER (...) = 1 is an error in both engines. Wrap it in a subquery or a CTE.

For N = 1 specifically, PostgreSQL has the shorter DISTINCT ON from lesson 1, and both engines have the LATERAL form from lesson 5. Which is fastest depends on N and your indexes; the window form is the one that generalises.

Running totals and moving averages

SELECT placed_at, shipping_cents,
       SUM(shipping_cents) OVER (ORDER BY placed_at, id)                       AS running,
       AVG(shipping_cents) OVER (ORDER BY placed_at, id
                                 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)     AS avg_7
FROM "order";

Any aggregate — SUM, AVG, COUNT, MIN, MAX — becomes a window function by adding OVER.

Frames: ROWS versus RANGE, and the default

The frame is which rows inside the partition the function actually sees. This is the part that produces wrong numbers silently.

When you write ORDER BY with no frame clause, the default is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE compares by value, so “current row” includes every peer with the same ORDER BY value. ROWS counts physical rows and stops at the current one.

With three orders sharing a timestamp, SUM(...) OVER (ORDER BY placed_at) gives all three the same running total — the sum including all peers. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives three increasing values. Neither is a bug; they answer different questions. Say which you meant.

The classic casualty is LAST_VALUE:

-- Almost never what the author wanted: with the default frame, the frame ends at
-- the current row, so "last value" is the current row's own value.
SELECT id, LAST_VALUE(status) OVER (PARTITION BY customer_id ORDER BY placed_at) FROM "order";

-- What they meant
SELECT id, LAST_VALUE(status) OVER (
         PARTITION BY customer_id ORDER BY placed_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) FROM "order";

FIRST_VALUE happens to be correct with the default frame, which is exactly why the LAST_VALUE version gets shipped: half of the pair works.

LAG and LEAD: the previous and next row

SELECT placed_at,
       LAG(placed_at)  OVER (PARTITION BY customer_id ORDER BY placed_at) AS prev_order,
       LEAD(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS next_order,
       placed_at - LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS gap
FROM "order";

The first row of each partition has no previous row, so LAG returns NULL. Both functions take a default: LAG(placed_at, 1, placed_at) uses the current value instead of NULL. This is how you compute gaps, deltas and “did the status change” without a self join.

NTILE and named windows

NTILE(n) splits the partition into n roughly equal buckets — quartiles, deciles:

SELECT id, cents, NTILE(4) OVER (ORDER BY cents) AS quartile FROM product;

Repeating a long OVER (...) is noise. Name it once — both engines support the WINDOW clause:

SELECT id,
       ROW_NUMBER() OVER w AS rn,
       SUM(shipping_cents) OVER w AS running
FROM "order"
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at, id);

Where the engines differ

	PostgreSQL	MySQL
Window functions	8.4+	8.0+ only
`FILTER (WHERE ...)` on a window aggregate	yes	no — use `CASE` inside the aggregate
`GROUPS` frame mode	yes	no
`PERCENTILE_CONT` / `PERCENTILE_DISC`	yes	no — approximate with `NTILE` or `ROW_NUMBER`
`ROWS` / `RANGE` frames	yes	yes

-- PostgreSQL
COUNT(*) FILTER (WHERE status = 'paid') OVER (PARTITION BY customer_id)

-- MySQL, same meaning
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id)

The CASE form works on both, so prefer it in anything portable.