CTEs and Recursive Queries

Name your intermediate steps with WITH, then walk a hierarchy with RECURSIVE.

• Advanced
• 15 min read
• 3 objectives

Before this lessonLesson 10: Numbers, Rounding and Ratios

A common table expression is a named subquery written before the query that uses it. Nothing it does is impossible with nested subqueries — it just reads top to bottom instead of inside out.

The same query, twice

SELECT name, spent FROM (
  SELECT c.name AS name, SUM(o.total) AS spent
  FROM customers c JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.name
) WHERE spent > 100;

name | spent
-----+------
Ada  | 155.5

WITH totals AS (
  SELECT c.name AS name, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.name
)
SELECT name, spent
FROM totals
WHERE spent > 100;

name | spent
-----+------
Ada  | 155.5

Same result, same plan. The second version names the intermediate step, which is the whole point.

Chaining CTEs

One WITH can define several CTEs separated by commas, and each may refer to the ones before it. This is how a four-step calculation stays readable.

WITH per_customer AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  GROUP BY customer_id
),
ranked AS (
  SELECT customer_id, spent,
         (SELECT COUNT(*) FROM per_customer p2 WHERE p2.spent > p1.spent) + 1 AS position
  FROM per_customer p1
)
SELECT c.name, r.spent, r.position
FROM ranked r
JOIN customers c ON c.id = r.customer_id
ORDER BY r.position;

name  | spent | position
------+------+---------
Ada   | 155.5 | 1
Grace | 80    | 2

Referencing a CTE more than once

A subquery you need twice has to be written twice. A CTE is named, so you can join it to itself — handy for comparing each row against the group.

WITH dept AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.name, e.department, e.salary, ROUND(d.avg_salary, 0) AS dept_avg,
       e.salary - d.avg_salary AS diff
FROM employees e
JOIN dept d ON d.department = e.department
ORDER BY e.department, diff DESC;

name     | department  | salary | dept_avg | diff
---------+------------+-------+---------+-------------------
Margaret | Design      | 140000 | 120000   | 20000
Alan     | Design      | 110000 | 120000   | -10000
Barbara  | Design      | 110000 | 120000   | -10000
Grace    | Engineering | 190000 | 153333   | 36666.66666666666
Ada      | Engineering | 150000 | 153333   | -3333.333333333343
Linus    | Engineering | 120000 | 153333   | -33333.33333333334

Recursive CTEs

A recursive CTE has two halves joined by UNION ALL: an anchor that produces the starting rows, and a recursive step that refers back to the CTE itself. It repeats until the step returns nothing.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

n
--
1
2
3
4
5

Walking an org chart

The same shape answers the real question: how deep is each person in the reporting tree, and who is above them? Start from the people with no manager, then join children to parents.

WITH RECURSIVE chart(id, name, manager_id, depth, path) AS (
  SELECT id, name, manager_id, 0, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1, c.path || ' > ' || e.name
  FROM employees e
  JOIN chart c ON e.manager_id = c.id
)
SELECT name, depth, path FROM chart ORDER BY path;

name     | depth | path
---------+------+-------------------
Grace    | 0     | Grace
Ada      | 1     | Grace > Ada
Linus    | 1     | Grace > Linus
Margaret | 0     | Margaret
Alan     | 1     | Margaret > Alan
Barbara  | 1     | Margaret > Barbara

Key takeaways

• WITH name AS (...) names a step so the final query reads top to bottom.
• Separate several CTEs with commas; later ones can use earlier ones.
• A CTE can be referenced twice; a subquery must be repeated.
• Recursive CTEs = anchor + UNION ALL + self-reference, and need a stop condition.

-- Write your solution here