7.2.2 — Aggregation, Window Functions and CTEs

A product manager asks for one table: every order, with its total, and what share that total is of the customer's lifetime spend.

GROUP BY cannot do it. Grouping collapses rows, and you need both the individual order and a number computed across the whole group. The old answer was to run the query twice and join it to itself. The right answer is a window function, and understanding why is the difference between writing SQL and fighting it.

1. GROUP BY collapses; that is its whole nature

SELECT customer_id, COUNT(*) AS orders, SUM(total_minor) AS spend
FROM orders
GROUP BY customer_id;

After GROUP BY customer_id, the rows for one customer no longer exist separately. There is one row per customer, and the only things you may select are the grouping columns and aggregates over the collapsed rows. Anything else has no single value to show.

SELECT customer_id, placed_at, SUM(total_minor)   -- error
FROM orders GROUP BY customer_id;

Which placed_at? There were forty. PostgreSQL, SQL Server and Oracle reject this. MySQL historically allowed it and returned an arbitrary value, which is how a whole generation of subtly wrong reports got written; modern MySQL rejects it too under ONLY_FULL_GROUP_BY, which is on by default since 5.7.

There is one legitimate exception. If you group by a table's primary key, every other column of that table is determined by it, so selecting them is unambiguous. PostgreSQL knows this and allows it:

SELECT c.id, c.name, c.email, COUNT(o.id) AS orders   -- (1)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

(1) c.name and c.email are functionally dependent on c.id (Chapter 7.1 section 5), so there is exactly one possible value. This saves listing every column and is worth knowing.

The aggregate functions and their null behaviour, which is where the quiet errors live:

Function	Skips NULL?	Empty group returns
COUNT(*)	No — counts rows	0
COUNT(col)	Yes	0
SUM	Yes	NULL, not 0
AVG	Yes — divides by non-null count	NULL
MIN / MAX	Yes	NULL
STRING_AGG / GROUP_CONCAT	Yes	NULL

SUM over no rows is NULL, not zero. A dashboard that shows a blank where it should show 0 is almost always this. COALESCE(SUM(x), 0) fixes it.

AVG divides by the count of non-null values. Ten rows with four nulls averages over six. If the nulls mean "zero" you must say so: AVG(COALESCE(score, 0)).

COUNT(DISTINCT x) is much more expensive than COUNT(*) — the engine must remember every value it has seen, via a sort or a hash. On a hundred million rows that is a real cost, and it is where the probabilistic structures from Chapter 4.29 (HyperLogLog) earn their place when an approximate answer is acceptable.

FILTER is the clean way to aggregate subsets in one pass:

SELECT
  COUNT(*)                                   AS all_orders,
  COUNT(*) FILTER (WHERE status = 'paid')    AS paid,       -- (1)
  SUM(total_minor) FILTER (WHERE status = 'refunded') AS refunded_value
FROM orders;

(1) One scan, several answers. The portable equivalent is SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), which does the same thing and reads worse. This shape — several conditional counts over one scan — replaces three separate queries and three round trips.

GROUPING SETS, ROLLUP and CUBE compute several grouping levels in one pass. GROUP BY ROLLUP (region, product) gives per-product rows, per-region subtotals, and a grand total, all in one result, with NULL marking the aggregated-away column. The GROUPING() function tells you whether a NULL means "subtotal here" or a real null value. This is exactly what a spreadsheet subtotal does, done once in the database instead of three times over the network.

2. Window functions: aggregate without collapsing

A window function computes a value across a set of rows related to the current row, and returns one value per row. The rows are not collapsed. That is the entire idea, and it answers the opening question directly.

SELECT
  o.id,
  o.customer_id,
  o.total_minor,
  SUM(o.total_minor) OVER (PARTITION BY o.customer_id) AS customer_spend,   -- (1)
  ROUND(100.0 * o.total_minor
        / SUM(o.total_minor) OVER (PARTITION BY o.customer_id), 1) AS pct   -- (2)
FROM orders o;

(1) OVER (…) is what makes it a window function. PARTITION BY customer_id divides the rows into groups the way GROUP BY would — but instead of collapsing each group, it computes the sum for the group and attaches it to every row in it. (2) So this row's share is computable in the same select list. This prints one row per order, each carrying both its own total and its customer's lifetime spend.

The three parts of an OVER clause:

OVER (
  PARTITION BY  …   -- which rows belong together (optional; default: all rows)
  ORDER BY      …   -- the order within the partition (needed by ranking and running totals)
  ROWS/RANGE    …   -- the frame: which rows within the partition count
)

Window functions run after WHERE, GROUP BY and HAVING, and before ORDER BY. That placement in the pipeline from Chapter 7.2.1 has two consequences you will hit immediately:

• You cannot put a window function in WHERE or HAVING. To filter on one, wrap the query in a CTE or a derived table and filter outside.
• A window function sees the rows that survived WHERE. Filtering to one month and then computing a "running total from the start of the year" gives a running total from the start of the month.

3. The ranking functions, and choosing between them

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,     -- 1,2,3,4
  RANK()       OVER (ORDER BY score DESC) AS rnk,    -- 1,2,2,4
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk    -- 1,2,2,3
FROM players;

With scores 90, 85, 85, 70:

name	score	ROW_NUMBER	RANK	DENSE_RANK
Ana	90	1	1	1
Ben	85	2	2	2
Cal	85	3	2	2
Dee	70	4	4	3

ROW_NUMBER never ties — it invents an order between equal rows, and which of Ben and Cal gets 2 is arbitrary unless you add a tie-breaker to the ORDER BY. RANK leaves gaps after a tie, the way sports rankings do. DENSE_RANK does not.

The most common real use is top-N-per-group, the other answer to the LATERAL question from Chapter 7.2.1:

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY placed_at DESC, id DESC) AS rn   -- (1)
  FROM orders o
)
SELECT * FROM ranked WHERE rn <= 3;                                    -- (2)

(1) Ranked within each customer, most recent first. The id DESC tie-breaker is not optional in production — two orders in the same millisecond otherwise pick a winner non-deterministically, and the report changes between runs. (2) The filter lives outside, because a window function cannot appear in WHERE.

NTILE(4) splits each partition into four roughly equal buckets — quartiles — and is how you label the top 25% of customers without knowing the cut-off value.

4. Offset functions: comparing a row to its neighbours

SELECT
  day,
  revenue_minor,
  LAG(revenue_minor)  OVER (ORDER BY day)               AS yesterday,   -- (1)
  revenue_minor - LAG(revenue_minor) OVER (ORDER BY day) AS change,
  LAG(revenue_minor, 7) OVER (ORDER BY day)             AS same_day_last_week,  -- (2)
  FIRST_VALUE(revenue_minor) OVER (ORDER BY day)        AS first_day    -- (3)
FROM daily_revenue;

(1) LAG reaches back to the previous row in the window order; LEAD reaches forward. The first row has no previous row, so yesterday is NULL and change is NULL with it — pass a third argument, LAG(x, 1, 0), if you want a default. (2) The offset can be any number, which makes week-over-week comparison a single expression rather than a self-join on day - 7. (3) FIRST_VALUE and LAST_VALUE reach to the ends of the frame — and LAST_VALUE has a trap explained next.

This is the shape that replaces self-joins. Before window functions, "compare each day to the previous day" meant joining the table to itself on d1.day = d2.day + 1, which fails on gaps and reads badly. LAG has neither problem: it means the previous row I have, not yesterday's date.

5. Frames — the part that produces wrong answers

The frame decides which rows inside the partition the function actually sees. Ignore it and you inherit a default that is not what you expect.

-- Running total, correct
SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM daily_revenue;

The rule to memorise: when you write ORDER BY in a window with no frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — and RANGE means rows with an equal ordering value are treated as one. With no ORDER BY, the default frame is the whole partition, which is what makes the section-2 example work.

The difference between ROWS and RANGE matters exactly when there are ties:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — every row up to and including this physical row.
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — every row whose ordering value is less than or equal to this row's. Two orders on the same day both get the full day's total, not a partial one.

So a running total over a column with duplicate values gives visibly wrong-looking numbers under the default. If you want a strict row-by-row accumulation, write ROWS explicitly. This one word is the most common window-function bug.

And the LAST_VALUE trap follows from the same default. LAST_VALUE(x) OVER (ORDER BY day) returns the current row's value, because the default frame ends at the current row — the last row it can see is this one. To get the partition's final value, say so:

LAST_VALUE(revenue) OVER (ORDER BY day
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

A moving average uses a bounded frame, and this is where frames pay for themselves:

AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  -- 7-day moving average

Reuse a window instead of repeating it. When several functions share one definition, name it:

SELECT day,
  SUM(revenue) OVER w AS running,
  AVG(revenue) OVER w AS avg_so_far
FROM daily_revenue
WINDOW w AS (ORDER BY day ROWS UNBOUNDED PRECEDING);

This is not only shorter — it guarantees the definitions cannot drift apart when someone edits one of them.

6. CTEs: naming the steps of a query

A CTE (common table expression) is a named subquery declared before the main query, with WITH.

WITH recent_orders AS (
  SELECT * FROM orders WHERE placed_at >= now() - interval '30 days'
),
per_customer AS (
  SELECT customer_id, COUNT(*) AS n, SUM(total_minor) AS spend
  FROM recent_orders GROUP BY customer_id
)
SELECT c.name, p.n, p.spend
FROM per_customer p JOIN customers c ON c.id = p.customer_id
WHERE p.spend > 100000
ORDER BY p.spend DESC;

The value is readability: each step has a name, and the query reads top to bottom like a pipeline instead of inside-out like nested subqueries. A five-level nested subquery and this are the same query; only one of them can be reviewed.

The materialization question, which is the one performance thing to know. Until version 12, PostgreSQL always materialized a CTE — computed it fully into a temporary result before the outer query ran — which acted as an optimisation fence: the planner could not push the outer WHERE down into it. That made CTEs a documented way to force a plan, and also a documented way to make queries much slower than the same logic written as a subquery.

Since PostgreSQL 12 a CTE used once is inlined by default, and you control it explicitly:

WITH heavy AS MATERIALIZED (…)      -- compute once, reuse; blocks pushdown
WITH light AS NOT MATERIALIZED (…)  -- inline into the outer query

Use MATERIALIZED when the CTE is expensive and referenced several times. Otherwise leave it alone. Other engines differ — SQL Server and Oracle have always inlined, MySQL 8 inlines by default — so "CTEs are slow" is engine-specific folklore, not a fact.

7. Recursive CTEs: walking a hierarchy

This is how you query a tree in SQL: an org chart, a category tree, a comment thread, a bill of materials.

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id, 1 AS depth              -- (1) anchor
  FROM employees
  WHERE id = 42

  UNION ALL                                            -- (2)

  SELECT e.id, e.name, e.manager_id, s.depth + 1       -- (3) recursive term
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
  WHERE s.depth < 20                                   -- (4) safety stop
)
SELECT * FROM subordinates ORDER BY depth, name;

(1) The anchor: the starting rows, run once. Here, employee 42. (2) UNION ALL joins the anchor to the recursive term. UNION (without ALL) also works and deduplicates each round, which is one way to survive a cycle — at the cost of a deduplication step every iteration. (3) The recursive term refers to the CTE's own name. Each round takes the rows produced by the previous round and finds their direct reports. (4) A depth guard. Add one. If the data contains a cycle — employee A reports to B reports to A, which happens after a bad import — the query runs until it exhausts disk. A depth column costs nothing and turns an outage into a wrong answer you can see.

Mechanically, it is breadth-first search — the same algorithm as Chapter 4.19.1, expressed declaratively. Round 1 is the anchor, round 2 is everything one hop away, and so on until a round produces no rows.

The alternatives, honestly. For deep or hot hierarchies, recursive CTEs get expensive because every read walks the whole tree. Two common shapes trade write cost for read cost: a materialized path column storing '/1/7/42/' so descendants are one LIKE '/1/7/%' index scan, and a closure table storing one row per ancestor-descendant pair so any subtree query is a plain join. Both must be maintained on every move. Use a recursive CTE first, and reach for those when measurement says to.

8. Putting it together

A cohort retention query, which uses most of this page at once:

WITH first_order AS (
  SELECT customer_id,
         date_trunc('month', MIN(placed_at)) AS cohort_month      -- (1)
  FROM orders GROUP BY customer_id
),
activity AS (
  SELECT o.customer_id,
         f.cohort_month,
         date_trunc('month', o.placed_at) AS active_month
  FROM orders o JOIN first_order f ON f.customer_id = o.customer_id
)
SELECT
  cohort_month,
  active_month,
  COUNT(DISTINCT customer_id) AS active,                          -- (2)
  ROUND(100.0 * COUNT(DISTINCT customer_id)
        / FIRST_VALUE(COUNT(DISTINCT customer_id))                -- (3)
          OVER (PARTITION BY cohort_month ORDER BY active_month), 1) AS retention_pct
FROM activity
GROUP BY cohort_month, active_month
ORDER BY cohort_month, active_month;

(1) Each customer's cohort is the month of their first order. (2) How many of that cohort were active in each later month. (3) The window function is applied on top of the aggregate — this is legal and often surprising: window functions run after grouping, so COUNT(DISTINCT …) inside FIRST_VALUE(…) OVER (…) means "the count for the first month of this cohort". Dividing by it gives retention as a percentage of the cohort's starting size.

The result is the retention triangle every analytics tool shows, computed in one query with no application code.

What the interviewer will push on

"What is the difference between GROUP BY and a window function?" GROUP BY collapses rows; a window function computes across related rows and returns a value per row. The tell is naming a problem only the window can solve — each order's share of its customer's total — rather than describing the syntax.

"Explain ROW_NUMBER, RANK and DENSE_RANK." 1,2,3,4 / 1,2,2,4 / 1,2,2,3. Then volunteer the production detail: ROW_NUMBER needs a deterministic tie-breaker in the ORDER BY, or the same query returns different rows tomorrow.

"How do you get the top 3 per group?" ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) in a CTE, filtered outside — because a window function cannot go in WHERE. Knowing why it must be wrapped is what is being checked. The LATERAL alternative is the bonus answer.

"What does the default window frame do?" With an ORDER BY and no frame clause the default is RANGE … CURRENT ROW, which treats tied ordering values as one row, so a running total over duplicate dates jumps. Write ROWS when you mean rows. Then mention LAST_VALUE returning the current row for the same reason — it is the same bug wearing a different hat.

"Are CTEs slower than subqueries?" Engine-specific. PostgreSQL before 12 always materialized them, which fenced the optimiser; from 12 a single-use CTE is inlined, with MATERIALIZED and NOT MATERIALIZED as explicit control. MySQL 8 and SQL Server inline. The wrong answer is a flat "yes, avoid CTEs" repeated from a 2015 blog post.

"How would you query a category tree?" A WITH RECURSIVE CTE — anchor, UNION ALL, recursive term — and immediately add that you would include a depth guard, because a cycle in the data otherwise runs forever. Then name materialized path and closure tables as the read-optimised alternatives, and what they cost on writes.

One thing to volunteer: point out that SUM over zero rows returns NULL rather than 0, so any dashboard aggregate wants COALESCE(SUM(x), 0). It is a one-line fix for a bug that reaches production constantly, and it shows you have shipped reports rather than only read about them.

Recall

• GROUP BY collapses rows: you may select only grouping columns and aggregates. Grouping by a primary key legally lets you select that table's other columns, because they are functionally dependent on it.
• SUM over no rows is NULL, not 0. AVG divides by the non-null count. COUNT(*) counts rows; COUNT(col) counts non-nulls. FILTER (WHERE …) gets several conditional aggregates from one scan.
• A window function returns one value per row while seeing a group of related rows — OVER (PARTITION BY … ORDER BY … ROWS …). It runs after WHERE/GROUP BY, which is why it can never appear in WHERE.
• ROW_NUMBER 1,2,3,4 · RANK 1,2,2,4 · DENSE_RANK 1,2,2,3. Always give ROW_NUMBER a tie-breaker or results change between runs.
• LAG/LEAD replace self-joins for neighbour comparison and are immune to gaps in the sequence.
• The default frame with ORDER BY is RANGE … CURRENT ROW, which lumps ties together — write ROWS for a true running total, and that same default is why LAST_VALUE returns the current row.
• CTEs name the steps of a query. PostgreSQL 12+ inlines a single-use CTE; MATERIALIZED forces the old compute-once behaviour and acts as an optimisation fence.
• WITH RECURSIVE is breadth-first search in SQL: anchor, UNION ALL, recursive term. Always add a depth guard — a cycle in the data otherwise runs until the disk fills. Materialized paths and closure tables trade write cost for fast subtree reads.

Self-test: Why can't a window function go in WHERE? · What does RANGE do differently from ROWS when values tie? · Why does LAST_VALUE return the current row by default? · What does SUM return over an empty group? · Name the two parts of a recursive CTE and the guard you must add · When is MATERIALIZED the right thing to write?

Next: 7.2.3 takes the queries you can now write and asks why one of them takes 4 seconds — reading EXPLAIN ANALYZE, the plan shapes that mean trouble, and the fixes that actually work.