Interview › Recursive CTE

SQL interview: walk a manager tree

A recursive CTE is the one time WITH is not just a name. Anchor, then join to itself.

Prompt. Starting from the person with no manager, list everyone under them with a depth.

Hint: UNION ALL join back to the CTE on manager_id

Your T-SQL

WITH tree AS (
  SELECT staff_id, full_name, manager_id, 0 AS depth
  FROM Staff
  WHERE manager_id IS NULL
  UNION ALL
  SELECT s.staff_id, s.full_name, s.manager_id, t.depth + 1
  FROM Staff s
  JOIN tree t ON t.staff_id = s.manager_id
)
SELECT * FROM tree;

OPTION (MAXRECURSION 100) if the graph is deep. A cycle will loop until that cap.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.

Clock in free