Drill 11 of 12 in HR questions
0

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…