From an employees table, write a single SQL query (a recursive CTE) that returns every employee together with their full management chain — the ordered list of their managers from the top of the org down to their direct manager.
Input
employees(id INTEGER, name VARCHAR, manager_id INTEGER, salary DOUBLE) — manager_id references employees.id. Top-level employees (e.g. the CEO) have manager_id NULL. Assume the hierarchy has no cycles.
Task
For each employee, walk up the management hierarchy and produce:
- depth — the number of managers above them (0 for a root).
- management_chain — an array of manager names ordered from the top-most ancestor down to the immediate manager (an empty array for a root). It must contain every ancestor, not only the direct manager.
- full_path — the management_chain with the employee's own name appended, joined by ' -> '.
Output
One row per employee, columns in this exact order:
id (INTEGER)name (VARCHAR)salary (DOUBLE)depth (INTEGER)management_chain (ARRAY of VARCHAR)full_path (VARCHAR)
Order by management_chain, then name.
Evaluation
Your query is executed against several hidden datasets in a DuckDB sandbox. Output is compared row-for-row against the reference result.
Worked example
employees: Alice (id 1, manager NULL, 200000), Bob (id 2, manager 1, 150000), Carol (id 3, manager 1, 140000).
Expected output:
| id | name | salary | depth | management_chain | full_path |
|----|-------|--------|-------|------------------|---------------|
| 1 | Alice | 200000 | 0 | [] | Alice |
| 2 | Bob | 150000 | 1 | [Alice] | Alice -> Bob |
| 3 | Carol | 140000 | 1 | [Alice] | Alice -> Carol|