SQL CTE (Common Table Expression)

1. Basic CTE (Named Subquery)

The simplest CTE replaces a subquery with a named step. This does not change what the query computes. It changes how you read and maintain it. For one-off usage, a subquery is fine. The moment you nest two levels or reference the same derived table twice, a CTE wins.

-- Find departments where average salary
-- exceeds the company average
WITH company_avg AS (
  SELECT AVG(salary) AS avg_salary
  FROM employees
)
SELECT
  department,
  ROUND(AVG(salary), 0) AS dept_avg,
  (SELECT avg_salary FROM company_avg) AS company_avg
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT avg_salary FROM company_avg);

2. Recursive CTE

Recursive CTEs reference themselves. They have an anchor member (the base case) and a recursive member (the iteration step), connected by UNION ALL. The engine runs the anchor once, then repeatedly runs the recursive member until it produces zero new rows. This solves hierarchical problems: org charts, category trees, bill-of-materials explosions, and graph paths.

-- Walk an employee hierarchy from the CEO down
WITH RECURSIVE org_tree AS (
  -- Anchor: start at the CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports of current level
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

3. Chained CTEs (Multi-Step Pipeline)

Each CTE can reference any CTE defined above it. This creates a data pipeline inside a single query: filter, then aggregate, then rank, then output. Each step has a name that documents what it does. This is the pattern interviewers use to test whether you can decompose a problem into clean steps.

WITH monthly_orders AS (
  -- Step 1: aggregate orders by user and month
  SELECT
    user_id,
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY user_id, DATE_TRUNC('month', order_date)
),
ranked AS (
  -- Step 2: rank users within each month
  SELECT *,
    RANK() OVER (
      PARTITION BY month ORDER BY total_spent DESC
    ) AS spend_rank
  FROM monthly_orders
),
top_spenders AS (
  -- Step 3: keep top 10 per month
  SELECT * FROM ranked WHERE spend_rank <= 10
)
-- Step 4: join back to users for names
SELECT
  u.name,
  ts.month,
  ts.order_count,
  ts.total_spent,
  ts.spend_rank
FROM top_spenders ts
INNER JOIN users u ON ts.user_id = u.id
ORDER BY ts.month, ts.spend_rank;

4. CTE vs Subquery

CTEs and subqueries often produce the same result. The choice is about readability, reuse, and (in some engines) performance. Here is the same query written both ways.

SELECT department, avg_sal
FROM (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
) dept_avgs
WHERE avg_sal > (
  SELECT AVG(salary) FROM employees
);

WITH dept_avgs AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
),
company_avg AS (
  SELECT AVG(salary) AS avg_sal
  FROM employees
)
SELECT d.department, d.avg_sal
FROM dept_avgs d
WHERE d.avg_sal > (SELECT avg_sal FROM company_avg);

5. CTE vs Temp Table

CTEs exist only for one statement. Temp tables persist for the session (or transaction). They solve different problems, but the choice confuses people. Here is when each one makes sense.

Writing WITH Before Every CTE

A common syntax error: writing WITH for each CTE instead of separating them with commas. Only the first CTE gets WITH. Every subsequent CTE is separated by a comma. This error is immediate feedback that you have not written many CTEs.

-- WRONG: multiple WITH keywords
WITH step1 AS (SELECT ...)
WITH step2 AS (SELECT ... FROM step1)  -- syntax error
SELECT * FROM step2;

-- CORRECT: one WITH, comma-separated
WITH step1 AS (
  SELECT ...
),
step2 AS (
  SELECT ... FROM step1
)
SELECT * FROM step2;

This is a fast rejection signal. Practice writing multi-CTE queries until the comma separator is automatic.

Missing UNION ALL in Recursive CTEs

The anchor and recursive members must be connected by UNION ALL. Using UNION (without ALL) works in some engines but deduplicates rows between iterations, which breaks hierarchy traversals where the same value appears at different depths. Worse, some engines reject UNION entirely in recursive CTEs.

-- WRONG: UNION deduplicates, may break recursion
WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS depth FROM nodes WHERE parent_id IS NULL
  UNION  -- should be UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

-- CORRECT: UNION ALL preserves all rows
WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS depth FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

Always use UNION ALL in recursive CTEs. If you need deduplication, apply DISTINCT in the final SELECT, not between iterations.

Over-Using CTEs Where a Subquery Is Cleaner

Not every query benefits from a CTE. A single scalar comparison (WHERE amount > (SELECT AVG(amount) FROM orders)) is clearer as an inline subquery. Wrapping it in a CTE adds syntax overhead with no readability gain. Interviewers notice when you add structure without purpose.

-- Overkill: CTE for a simple scalar
WITH avg_order AS (
  SELECT AVG(amount) AS val FROM orders
)
SELECT * FROM orders
WHERE amount > (SELECT val FROM avg_order);

-- Simpler: inline subquery
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

Use CTEs when they add clarity or enable reuse. For one-off scalar comparisons, keep the subquery inline. Show the interviewer you pick the right tool for each situation.