CTEs and Recursive CTEs

A CTE is a named subquery written before the query that uses it. The plain form is about readability; the recursive form does something no other SQL construct can — walk a hierarchy of unknown depth.

The basic form

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.first_name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.department = e.department
WHERE e.salary > d.avg_salary;

Same result as an inline subquery, but the step has a name and reads top to bottom. That matters more than it sounds: an interviewer reading a nested three-level subquery is working harder than one reading three named steps.

CTEs also chain, and later ones can reference earlier ones:

WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
    FROM orders
    GROUP BY 1
),
with_change AS (
    SELECT month, revenue,
           LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS pct_change
FROM with_change
WHERE prev_revenue IS NOT NULL;

That is the shape of most analytics answers: aggregate, then compare, then present — one CTE per verb.

CTE, subquery or view

Referencing a CTE twice does not guarantee it is computed once — most engines inline CTEs and may evaluate them per reference. PostgreSQL materialised them until version 12 and now inlines by default, with WITH … AS MATERIALIZED to force the old behaviour. If a CTE is expensive and used repeatedly, a temp table is the honest answer.

Recursive CTEs

The part worth real study. A recursive CTE has two halves joined by UNION ALL:

WITH RECURSIVE org_chart AS (
    -- ANCHOR: where to start
    SELECT employee_id, first_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- RECURSIVE: joins the CTE back to the base table
    SELECT e.employee_id, e.first_name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level;

The engine runs the anchor once, then repeatedly runs the recursive half against **only the rows produced by the previous iteration**, until an iteration produces nothing.

RECURSIVE is required in PostgreSQL, MySQL and SQLite; SQL Server and Oracle omit the keyword.

Generating a series

The other everyday use — producing rows that do not exist in any table, which is how you fill gaps in a report:

WITH RECURSIVE dates AS (
    SELECT DATE '2024-01-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day' FROM dates WHERE day < DATE '2024-12-31'
)
SELECT d.day, COALESCE(SUM(o.total), 0) AS revenue
FROM dates d
LEFT JOIN orders o ON o.order_date = d.day
GROUP BY d.day
ORDER BY d.day;

The LEFT JOIN from the generated dates is what makes days with no orders appear as zero rather than vanishing — a GROUP BY over the orders table alone can never produce a row for a day that has none. PostgreSQL's generate_series() does this without recursion and is preferable when available.

Guarding against infinite recursion

A cycle in the data — an employee who is transitively their own manager — makes the recursion never terminate. Two defences:

-- 1. A depth limit
WHERE oc.level < 100

-- 2. A visited path (PostgreSQL 14+ has CYCLE syntax; this is portable)
SELECT …, oc.path || e.employee_id
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
WHERE NOT e.employee_id = ANY(oc.path)

MySQL caps iterations at cte_max_recursion_depth (1000) and errors rather than hanging. Mentioning the cycle risk unprompted is a strong signal in an interview.

Mistakes that cost the interview

• `UNION` instead of `UNION ALL` in the recursive half. UNION deduplicates on every

iteration, which is slow and can mask a cycle rather than fix it.

• Recursive term referencing the CTE twice. Most engines forbid it.
• No termination condition, or none that the data guarantees.
• Expecting a CTE to be computed once. It usually is not.
• Reaching for recursion where a self-[JOIN](/learn/sql/joins) would do. One level of

hierarchy needs no recursion at all.

Practise these on the SQL sheet.