Common Table Expressions

A common table expression is a named subquery written before the statement that uses it. The keyword is WITH.

WITH spend AS (
  SELECT u.user_id, u.name, SUM(o.amount) AS spent
  FROM users u
  JOIN orders o ON o.user_id = u.user_id
  WHERE o.status = 'paid'
  GROUP BY u.user_id, u.name
)
SELECT name, spent FROM spend WHERE spent > 200;

That is the derived table from the last module, unchanged in what it computes. What changed is the reading order: the step that happens first is now written first, with a name, instead of being buried in the middle of a FROM clause.

The name behaves exactly like a table for the rest of the statement. It exists only for this statement — nothing is stored, and nothing survives the query.

Your task: using WITH, return name and spent for every user whose total paid spend is over 200, ordered by spent descending.

Several CTEs go in one WITH, separated by commas — and each may use the ones before it. That is what turns a pile of nesting into a sequence of steps.

WITH spend AS ( … ),
     benchmark AS (SELECT AVG(spent) AS avg_spent FROM spend)
SELECT …

Note there is only one WITH, at the very top. The comma introduces the second name; writing WITH again is a syntax error.

This is the two-level aggregation from 3-05 — the average of per-user totals, 210.11 — with the two levels finally readable as two named steps rather than one query inside another.

Your task: return name and spent for every user whose paid spend beats the average paid spend per user, ordered by spent descending. Use two CTEs.

Here is the one hard capability difference, rather than a matter of taste. A CTE can be referenced more than once. A derived table cannot — you would have to paste the entire subquery in again, and then keep the two copies in step forever.

WITH spend AS ( … )
SELECT (SELECT COUNT(*) FROM spend)             AS n,
       (SELECT ROUND(AVG(spent), 2) FROM spend) AS avg_spent;

One definition, two uses. Any query that needs to compare a set against a summary of itself has this shape, and it is where WITH stops being cosmetic.

Your task: define a CTE of per-user paid spend, then return two columns from it in a single row: how many users have any paid spend as n, and their average spend rounded to two decimals as avg_spent.

A CTE is a table for the rest of the statement, so everything from Part 3 applies to it unchanged — it can be joined, left-joined, grouped and filtered.

This is the usual way to attach an aggregate back onto the rows it came from without a fan-out. Aggregate in the CTE, join the result, and every row keeps its own identity.

And the outer join still matters: the two users who never ordered are absent from a spend CTE built on orders, so keeping them needs LEFT JOIN and COALESCE exactly as in 3-02.

Your task: return every user's name, country and paid spent — 0 where they have none — by joining a spend CTE to users. Order by spent descending, then name.

Now the genuinely new capability. A self join reaches one level of a hierarchy — a user and their referrer. Two self joins reach two. But "everyone in the referral tree, however deep" cannot be written that way at all, because the depth is not known when the query is written.

WITH RECURSIVE is the answer, and it has exactly two parts joined by UNION ALL:

WITH RECURSIVE tree AS (
  SELECT user_id, name, 1 AS depth              -- anchor: runs once
  FROM users WHERE referred_by IS NULL
  UNION ALL
  SELECT u.user_id, u.name, t.depth + 1        -- recursive: re-runs
  FROM users u JOIN tree t ON u.referred_by = t.user_id
)
SELECT * FROM tree;

The anchor runs once and seeds the result — here, the four users who arrived on their own. The recursive term then runs against only what the previous pass produced, adding the people they referred, and repeats until a pass returns nothing. That empty pass is what stops it.

The word RECURSIVE goes after WITH, once, even if only one of several CTEs is recursive.

Your task: return name and depth for every user in the referral tree, ordered by depth then name.

A recursive CTE does not need a table at all. It can generate rows — which makes it the standard way to build a complete list of months, so a report can show a zero for a month in which nothing happened.

WITH RECURSIVE m AS (
  SELECT DATE '2023-01-01' AS mon
  UNION ALL
  SELECT (mon + INTERVAL '1 month')::date FROM m WHERE mon < DATE '2023-06-01'
)
SELECT mon FROM m;

The WHERE in the recursive branch is the termination condition, and it is not optional. It is what makes a pass eventually return no rows.

Leave it out and the query never finishes. In this dojo that means the worker is killed and you press Run again; on a real database it means a query someone has to go and cancel. Postgres will not detect it for you.

The ::date cast matters too — adding an INTERVAL to a DATE gives a TIMESTAMP, and without the cast the column type changes between the two branches.

Your task: generate the first six months of 2023, one row each, as mon, ordered by mon.

Recursion is the portable way to build a series. On Postgres there is a far shorter one, and it is what a Postgres analyst would actually write.

SELECT g::date AS mon
FROM generate_series(DATE '2023-01-01', DATE '2023-12-01', INTERVAL '1 month') g;

generate_series is a set-returning function — it goes in FROM and produces rows. Start, stop, step; the stop value is included. It works on integers and timestamps alike.

This is Postgres-specific. It has no equivalent in most other engines, which is exactly why the recursive form was worth learning first — but when the target is Postgres, this is clearer and faster, and there is no termination condition to get wrong.

Your task: return all twelve months of 2023 as mon, using generate_series, ordered by mon.

The argument for WITH is mostly about the next person to read the query, which includes you in six months. A well-named CTE says what a step is for; a nested subquery only says what it does.

Compare "the subquery in the FROM clause of the left join" with active_buyers. The second one can be discussed in a meeting.

Your task: a re-engagement list. Define a CTE named buyers holding the user_id of everyone with at least one paid order, then return user_id and name for every user not in it, ordered by user_id.

Three users qualify. Watch the negation — this is the module where NOT IN bit, and a CTE does nothing to protect you from it.

Everything in Part 3 has been a different way of bringing rows together. A rough guide to which:

Your task: put the whole part together. Using CTEs, return each country, the number of users in it as users, and its total paid revenue — including countries whose users have never bought anything, which must show 0. Order by revenue descending, then country.