How would you list all reports under a manager, at any depth, using SQL?
Use a recursive CTE: start with the manager's direct reports, then repeatedly join employees whose manager_id matches the previous level. Track depth and a path array to show levels and guard against cycles.
Explain it simply
A company chart is a tree: the CEO has managers, who have team leads, who have engineers. A normal join can go one level. A recursive CTE keeps walking down level by level, collecting everyone under the starting person.
Example
CREATE TABLE employees (
id int PRIMARY KEY,
name text,
manager_id int REFERENCES employees (id)
);
WITH RECURSIVE team AS (
SELECT id, name, manager_id, 1 AS depth, ARRAY[id] AS path
FROM employees
WHERE manager_id = 1 -- direct reports of employee 1
UNION ALL
SELECT e.id, e.name, e.manager_id, t.depth + 1, t.path || e.id
FROM employees e
JOIN team t ON e.manager_id = t.id
WHERE NOT e.id = ANY (t.path) -- stop if a cycle exists
)
SELECT repeat(' ', depth - 1) || name AS org, depth
FROM team
ORDER BY path;path gives a natural order (each employee after their manager) and protects against bad data where someone ends up managing their own manager.
The same shape works for category trees, comment threads and bill-of-materials.
What to say in the interview
"I use a recursive CTE with the manager's direct reports as the anchor and a join on manager_id for the step, tracking depth and a path array for ordering and cycle protection. A column index on manager_id keeps each step fast."
Common mistakes
- Not indexing manager_id, so each level scans the whole table.