SQL CTE: the WITH clause, explained with real queries

WITH recent_orders AS (
    SELECT order_id, customer_id, amount, ordered_at
    FROM orders
    WHERE ordered_at >= current_date - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM recent_orders
GROUP BY customer_id;

recent_orders exists only while this statement runs. Nothing is created on disk, no cleanup, no permissions to grant.

This is where CTEs earn their keep — each step is named, and each can use the previous one.

WITH paid AS (
    SELECT * FROM orders WHERE status = 'paid'
),
per_customer AS (
    SELECT customer_id,
           SUM(amount)      AS revenue,
           COUNT(*)         AS orders,
           MIN(ordered_at)  AS first_order
    FROM paid
    GROUP BY customer_id
),
ranked AS (
    SELECT *,
           NTILE(4) OVER (ORDER BY revenue DESC) AS revenue_quartile
    FROM per_customer
)
SELECT c.name, r.revenue, r.orders, r.revenue_quartile
FROM ranked r
JOIN customers c ON c.id = r.customer_id
WHERE r.revenue_quartile = 1
ORDER BY r.revenue DESC;

Try rewriting that with nested subqueries and you will understand why analytics engineers write almost everything this way — it is also exactly how a dbt model is structured.

An org chart with each employee's depth below the CEO:

WITH RECURSIVE org AS (
    -- anchor: the top of the tree
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- recursive member: everyone reporting to a row we already found
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
    WHERE o.level < 20          -- always bound the recursion
)
SELECT level, name FROM org ORDER BY level, name;

The level < 20 guard is not optional in production. A cycle in the data (A manages B, B manages A) turns an unbounded recursive CTE into a query that never finishes.

Same pattern, different use — generating a gapless date spine for reporting:

WITH RECURSIVE dates AS (
    SELECT DATE '2026-01-01' AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < DATE '2026-12-31'
)
SELECT d FROM dates;

Modern engines inline CTEs, so the plan is normally the same as the equivalent subquery. The trap is referencing an expensive CTE several times: some engines re-evaluate it each time. When that happens, materialize it on purpose.

-- PostgreSQL: force one evaluation, reuse the result
WITH heavy AS MATERIALIZED (
    SELECT customer_id, SUM(amount) AS revenue
    FROM orders GROUP BY customer_id
)
SELECT * FROM heavy WHERE revenue > 1000
UNION ALL
SELECT * FROM heavy WHERE revenue < 10;

Always confirm with EXPLAIN ANALYZE instead of guessing — that habit is what separates a senior from someone who "heard CTEs are slow".

"A CTE is a named temporary result set scoped to one statement. I use them to break a transformation into readable steps and to express recursion over hierarchies. They are usually inlined, so I don't treat them as an optimization — if a heavy CTE is referenced multiple times I materialize it deliberately and verify with EXPLAIN." Pair that with window functions and you can solve most SQL rounds. More practice in our SQL interview questions.