Recursive CTEs: org charts, paths and generated rows

Part of the free LogicWiz SQL & Databases course, Module 12: CTEs and recursive queries. The queries and practice run in the app on real Postgres.

The CEO asks: "List everyone who reports to Harish, directly or through someone." One self join finds his direct reports, two joins find their reports... but how many joins do you write when you don't know how deep the tree goes?

You'll learn to

• Write WITH RECURSIVE with an anchor part and a recursive part
• Walk an org chart down (all reports) and up (chain of managers)
• Track depth and build a path string
• Generate rows such as numbers and calendar dates, and stop safely

Learn

How a recursive CTE runs

A recursive CTE has two queries joined by UNION ALL:

1. The anchor: the starting rows (Harish).
2. The recursive part: it refers to the CTE itself and finds the next rows from the rows found in the last round (people whose manager is in the last round).

Postgres runs the anchor, then repeats the recursive part on only the new rows, round after round, and stops when a round adds no rows. The result is all rounds stacked together.

The smallest example: count to 5

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

Round 0 gives 1. Each round adds x + 1 while the previous x is below 5. When x = 5, the WHERE keeps nothing, no new row is made, and it stops.

Everyone under Harish

WITH RECURSIVE team AS (
  SELECT emp_id, name, 0 AS level
  FROM employees
  WHERE emp_id = 2
  UNION ALL
  SELECT e.emp_id, e.name, t.level + 1
  FROM employees e
  JOIN team t ON e.manager_id = t.emp_id
)
SELECT name, level
FROM team
ORDER BY level, name;

The anchor is Harish (level 0). Each round joins employees whose manager_id is someone found in the last round, and adds 1 to the level.

Round by round

Paths: the whole chain as text

WITH RECURSIVE org AS (
  SELECT emp_id, name, name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, o.path || ' > ' || e.name
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT path
FROM org
ORDER BY path;

Each round adds its name to the parent's path. The ::TEXT cast matters: the anchor fixes the column type, and VARCHAR(40) would be too short for long paths.

A recursive CTE stops only when a round adds no rows. If the data has a cycle (A manages B, B manages A) or the recursive part has no stop condition, it runs forever. Guard it: add a depth limit (WHERE t.level < 20), keep an array of visited ids, or use Postgres's CYCLE emp_id SET is_cycle USING path clause. UNION instead of UNION ALL also stops repeats of identical rows.

Generating dates: a calendar with no gaps

WITH RECURSIVE days AS (
  SELECT DATE '2025-03-22' AS day
  UNION ALL
  SELECT day + 1 FROM days WHERE day < DATE '2025-03-31'
)
SELECT d.day, COUNT(m.match_id) AS matches
FROM days d
LEFT JOIN matches m ON m.match_date = d.day
GROUP BY d.day
ORDER BY d.day;

Real data has gaps: no match on 25 or 28 March. A generated calendar LEFT JOINed to the data shows those days as 0. Postgres also has generate_series(DATE '2025-03-22', DATE '2025-03-31', INTERVAL '1 day') for this.

Classic interview asks: "find all subordinates of a manager", "find the chain of managers up to the CEO", "level of every employee", "generate the missing dates". Explain anchor, recursive member and termination in one breath each. The keyword is WITH RECURSIVE in Postgres and MySQL 8; SQL Server and Oracle write just WITH.

In one line

WITH RECURSIVE = anchor rows UNION ALL a part that joins the CTE to find the next rows; it repeats until a round adds nothing, so always make sure it can stop.

Interview corner

How does a recursive CTE work?

It has an anchor query (the starting rows) and a recursive query that references the CTE, joined by UNION ALL. Postgres runs the anchor, then runs the recursive part on the rows produced by the previous round, again and again, until a round produces no rows. The result is all rounds together.

Find all employees under a given manager, at any depth.

WITH RECURSIVE t AS (SELECT emp_id FROM employees WHERE manager_id = :id UNION ALL SELECT e.emp_id FROM employees e JOIN t ON e.manager_id = t.emp_id) SELECT * FROM t; Add a level + 1 column to know the depth.

How do you stop a recursive CTE from looping forever?

Make sure the recursive part eventually returns nothing: a WHERE on a counter or depth (level < 20), tracking visited ids in an array and skipping them, or Postgres 14's CYCLE clause. UNION (not ALL) also stops when rows repeat exactly.

How would you fill in missing dates in a report?

Generate every date (recursive CTE or generate_series), LEFT JOIN the data to it, and use COALESCE or COUNT(column) so empty days show 0.