Naming a step of the query

A query with three nested subqueries is correct and unreadable. A common table expression gives each step a name, and the query reads top to bottom.

The shape

WITH recent_orders AS (
    SELECT * FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM recent_orders
    GROUP BY customer_id
)
SELECT c.name, t.total
FROM customer_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.total > 10000
ORDER BY t.total DESC;

Each block is named, each may use the ones before it, and the final query reads as a sentence. The same thing written with nesting is a single expression four levels deep that has to be read inside out.

Recursive: walking a hierarchy

This is the part SQL cannot do any other way. An employee table where each row points at a manager, and you want everyone under one person at any depth:

WITH RECURSIVE reports AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE id = 7              -- the anchor

    UNION ALL

    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM employees e
    JOIN reports r ON e.manager_id = r.id    -- the step
)
SELECT * FROM reports;

The anchor produces the starting row. The second half joins the table back against the results so far, producing the next level. It repeats until a round produces nothing.

This is how you query a category tree, a threaded comment list, a bill of materials or a road network, and without it those need a loop in application code with one query per level.

The guard rail

A recursive CTE over data containing a cycle runs forever. If the hierarchy is not guaranteed acyclic, track the path and stop when a node repeats, or add a depth limit.

One caveat worth knowing

In some databases a CTE is an optimisation fence: it is computed once, in full, rather than being folded into the surrounding query. That is sometimes exactly what you want and sometimes much slower than the equivalent subquery. Postgres did this until version 12 and now inlines by default unless you write MATERIALIZED.