Postgres Practice Lesson 12 of 12

Recursive CTEs (capstone)

Org charts, category trees, folder structures - hierarchies get stored in SQL as a table pointing at itself: each row carries the id of its parent. Simple to store, awkward to query: "everyone under Ken" includes Ken's direct reports, their reports, and so on - a walk of unknown depth, which no fixed number of JOINs can express.

WITH RECURSIVE is SQL's answer. It has three parts, always the same shape:

WITH RECURSIVE reports AS (
  SELECT ...   -- 1. base case: where the walk starts
  UNION ALL
  SELECT ...   -- 2. step: joins the table to `reports` itself
)
SELECT ...     -- 3. use the accumulated result

The engine runs the base case, then applies the step to the rows it just produced, then applies it again to those results - until a step produces no new rows. The magic line is the step's join: it references the CTE being defined (JOIN reports r ON e.manager_id = r.id - "employees whose manager is someone already in the result").

You have an employees table: id, name, manager_id (NULL for the CEO). Rosa runs the company; Ken and Ana report to her; Ben and Cho report to Ken; Dev reports to Ana; Eli reports to Ben.

Your task: list the name of every employee under Ken (id 2) at any depth - his direct reports and their reports, but not Ken himself. Order by name.

You'll practice:

The base-case / UNION ALL / recursive-step shape
A step that joins the table back to the CTE being built

-- Everyone under Ken (id 2), any depth. A plain query only gets one level:
SELECT id, name FROM employees WHERE manager_id = 2;
-- Eli (who reports to Ben, who reports to Ken) is missing. Recursion fixes that.