Subqueries, CTEs and Recursive CTEs

Karim asks a question with a hidden second question inside it: “Which customers spent more than the average customer in 2025?” To answer it you need the average first, which itself needs per-customer revenue. That’s three steps: revenue per customer, the average of those, then the comparison. SQL gives you two ways to nest steps, subqueries and CTEs, and the choice decides whether the next analyst can read your work.

Three kinds of subquery

You’ve already used two of them. A derived table is a subquery in FROM that acts like a table (the per-order revenue that fixed the fan-out). A correlated subquery refers to the outer row and runs per row (the NOT EXISTS anti-join). The third is the scalar subquery: a query that returns exactly one value, usable anywhere a value is.

Here’s Karim’s question written with nesting only:

SELECT COUNT(*) AS above_average_customers
FROM (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
    AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
  GROUP BY o.customer_id
) AS r
WHERE r.revenue > (
  SELECT AVG(revenue) FROM (
    SELECT SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
      AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
    GROUP BY o.customer_id
  )
);

It works: 140 customers. It’s also hard to read, and the revenue logic is written twice. When someone changes the definition of revenue next quarter, they’ll update one copy and not the other.

The same analysis as CTEs

A common table expression names a query with WITH name AS (...), and later steps refer to it like a table:

WITH revenue_2025 AS (
  -- grain: one row per customer who bought in 2025
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
    AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
  GROUP BY o.customer_id
),
benchmark AS (
  SELECT AVG(revenue) AS avg_revenue FROM revenue_2025
)
SELECT
  COUNT(*) AS buyers,
  SUM(r.revenue > b.avg_revenue) AS above_average,
  ROUND(b.avg_revenue, 2) AS avg_revenue
FROM revenue_2025 AS r
CROSS JOIN benchmark AS b;

Now the analysis reads like the sentence it answers: revenue per customer, the benchmark, the comparison. Revenue is defined once. And while building it you can replace the final SELECT with SELECT * FROM revenue_2025 LIMIT 5 to inspect any step, which is the single best debugging habit for long SQL. CROSS JOIN is safe here because benchmark has exactly one row. In SQLite a comparison is 1 or 0, so SUM(condition) counts TRUE rows; in PostgreSQL you’d write COUNT(*) FILTER (WHERE ...).

So 140 of 418 buyers sit above the 781.84 average. That’s typical of revenue data: a minority of large customers pulls the mean up, which is why you’ll often report the median beside it.

Recursive CTEs: walking a hierarchy

Rania’s reorganisation request comes back: “List everyone in Peter Weber’s engineering organisation, at any level, with how far below him they sit.” A self-join gets you one level. Peter’s org has two, and another department might have five. A recursive CTE walks as deep as the data goes:

WITH RECURSIVE org AS (
  -- anchor: where the walk starts
  SELECT employee_id, first_name, title, 0 AS depth, first_name AS path
  FROM employees
  WHERE employee_id = 4
  UNION ALL
  -- recursive step: people who report to anyone found so far
  SELECT e.employee_id, e.first_name, e.title, org.depth + 1,
         org.path || ' > ' || e.first_name
  FROM employees AS e
  JOIN org ON e.manager_id = org.employee_id
)
SELECT depth, path, title
FROM org
ORDER BY path;

The structure is always the same. The anchor query runs once and produces the starting rows. The recursive query joins employees to org itself, but on each iteration it only sees the rows added by the previous iteration. Each pass adds the next level down, and the walk ends when an iteration finds nobody.

depth and path are carried through the recursion: each new row computes its values from the parent row it joined to. Sorting by path prints the result as an indented tree. The same pattern rolls costs up a category tree, follows referral chains, or expands a bill of materials.

With CTEs you can name every step. The next lesson adds a tool that lets each row see its neighbours without collapsing anything: window functions.