Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

Q: Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

**Target Audience:** Developers who know JOINs and have seen a basic recursive CTE; the applied interview version of "walk a hierarchy". **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15; every query below was run on both and returned the same rows. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and plan below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First HR asks: *"Raj is leaving. Who is affected?"* Not just the two engineering managers who report to him, but their engineers, and the intern one of those engineers mentors. In the database each person only knows one thing: who their *own* manager is. Nobody stores "Zoe is three levels under Raj". So you work like a p

The problem

Question presented to candidate: "We store employees in one table with a manager_id column. Given a manager, write a query that returns everyone who reports to them, directly or through other managers, with how many levels down each person is."

What a strong answer should cover:

Why a plain self join is not enough: each join adds exactly one level, and the depth of the tree is unknown
A recursive CTE: anchor = the direct reports (manager_id = :id), recursive member joins employees.manager_id = reports.id, carrying a level column
Deciding whether the manager themself is in the result (anchor on id = :id with level 0, or on manager_id = :id with level 1)
Presentation: a path column to print the chart in tree order, and aggregating the walk for headcount per manager
Performance: an index on manager_id turns each iteration into index lookups (PostgreSQL shows a Nested Loop with an Index Scan per working-table row)
Robustness: a depth guard or cycle check when the data could contain a loop; alternatives for very read-heavy trees (closure table, materialized path)

Clarifying questions expected:

Should the result include the manager, and do you want the depth or the full chain for each person?
Can the data contain cycles or people with several managers (a graph, not a tree)?
How big is the org and how often is this run: per request, or for a report?

Code / implementation expected: Yes — a recursive CTE listing all reports with their level, plus ideally the tree-ordered view or the headcount roll-up.

Schema used by the examples:

recursive-cte
hierarchy
org-chart

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who know JOINs and have seen a basic recursive CTE; the applied interview version of "walk a hierarchy". Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15; every query below was run on both and returned the same rows.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and plan below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

HR asks: "Raj is leaving. Who is affected?" Not just the two engineering managers who report to him, but their engineers, and the intern one of those engineers mentors. In the database each person only knows one thi

The solution is hidden so you can try first

12 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLAll direct and indirect reports of Raj (id 2), with their level

SQLThe same walk printed as an indented org chart, in tree order

SQLHeadcount roll-up: total and direct reports for every manager

PostgreSQL plan: each iteration uses the index on manager_id

SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
WITH RECURSIVE reports(id, name, level) AS (
  SELECT id, name, 1 FROM employees WHERE manager_id = 2
  UNION ALL
  SELECT e.id, e.name, r.level + 1
  FROM employees AS e
  JOIN reports AS r ON e.manager_id = r.id
)
SELECT name, level FROM reports;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.