mediumDSA

Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

Q: Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

A recursive CTE has an *anchor* (base rows) `UNION ALL` a *recursive* term that joins the CTE back to the table. Start at the manager, repeatedly join `manager_id = cte.id`, and track `depth`/`path`. Recursive CTE: anchor (level 0) UNION ALL recursive step → traverses tree until no rows Anchor WHERE manager_id IS NULL CEO depth=0 base case Recursive <text x="

86 views

Understand the problem

Anchor + recursive term to traverse a manager → employee hierarchy and compute depth/path.

recursive-ctehierarchyorg-chart

Attempt it yourself

Sketch your approach before reading the solution — that's what interviews test.

Nudge consolestandby

Stuck? Beam a request up — the console returns a conceptual nudge that guides your logic without spoiling the implementation.

Study the solution

A recursive CTE has an anchor (base rows) UNION ALL a recursive term that joins the CTE back to the table. Start at the manager, repeatedly join manager_id = cte.id, and track depth/path.

Solution ready — 2 min read

Classified // press E to declassify

Join the discussion

Discussion (0)

No responses yet. Be the first to share what you think.

Transmission complete // awaiting log

KEEP THE
STREAK ALIVE.

Dossier 47 of 70 decoded in the SQL track. One more won't hurt.

Back to track

Next up→

What is an index and how does it speed up queries?

Continue