Everyone beneath one manager

Constraints

1. 1The depth is not known in advance. A query with a fixed number of self joins answers this dataset and breaks the day somebody adds a level, so it does not count as an answer.
2. 2Use WITH RECURSIVE. The anchor selects the direct reports; the recursive term joins the table back to the rows the CTE has already produced.
3. 3Do not include the VP herself — the question is who reports to her, not who she is.
4. 4depth must be computed as you walk, not hard-coded from the titles: the tree does not line up with seniority, and one staff engineer sits at the same level as two managers.
5. 5Order by depth, then employee_id.

Worked example

The anchor returns Eve Marsh (5) and Farid Haddad (6) at depth 1. The recursive term then finds everyone whose manager_id is 5 or 6 — Ivy Chen, Jonas Weber and Kira Novak at depth 2 — and repeats with those, reaching Omar Diaz, Priya Nair and Quinn Doyle at depth 3. It keeps going: Tara Singh and Uzo Eze at depth 4, and Vik Sharma at depth 5. Eleven people in total. Three self joins would have stopped at depth 3 and returned eight rows — a plausible-looking answer that silently omits the three deepest people. That is the failure this question exists to make visible.

What this tests

Whether you can write a recursive CTE at all, and whether you noticed that the depth of the tree is a property of the data rather than of the query.