Interview Question Bank · WITH RECURSIVE · carrying the root · pruning in the recursive step
11. Org depth and size under each VP
HardHRQuestion #191
Concept: WITH RECURSIVE · carrying the root · pruning in the recursive stepshow
A recursive CTE can carry a column from its anchor row all the way down, such as the id of the person the walk started from, so one query explores several subtrees and you can GROUP BY that root afterwards. Keeping the root itself as a depth-0 row means a root with nothing below still produces a group. A WHERE in the recursive step prunes as it walks: rows it rejects aren't counted, and neither is anything only reachable through them.
Example (different data):
WITH RECURSIVE tree AS ( SELECT id AS root_id, id, 0 AS depth FROM folders WHERE parent_id IS NULL UNION ALL SELECT t.root_id, f.id, t.depth + 1 FROM folders f JOIN tree t ON f.parent_id = t.id ) SELECT root_id, MAX(depth) AS depth, COUNT(*) AS folders FROM tree GROUP BY root_id;
Your task
Before a reorg, Quillmoor's CEO wants to see how deep and how large each VP's organization is. Former employees still have a manager_id, but they're no longer part of anyone's org.
For every current VP (title starting with 'VP'), return vp (their name), levels_below (how many reporting levels sit under them: 1 if only direct reports, 0 if nobody reports to them) and org_size (current employees who report to them directly or indirectly). Count current employees only. Sort by org_size descending, then vp.
Starting Postgres in your browser…
Checking your access…