Org Chart with Reporting Paths
Hard · Recursive Queries
A recursive CTE can carry values down the tree as it walks: a depth counter that grows by 1 per level, and a text path that grows by concatenation (t.path || ' > ' || e.title).
In employees, manager_id points to the employee's manager; people with no manager (NULL) are at the top. For every employee, return:
- employee: the name
- depth: 0 for someone with no manager, 1 for their direct reports, and so on
- path: the job titles from the top of the chain down to this employee, joined with ' > ', like CEO > CTO > Dev
Sort by depth, then employee.