codejump
MediumSQL

Org Chart Ancestry Path

SQLDatabasesRecursive CTE

Write a query that returns every employee's full path from the top of
the org chart down to them, as a single string joined with > .

Table: `employees`

idnamemanager_id
1CEONULL
2VP Eng1
3VP Sales1
4Eng Manager2
5Engineer A4
6Engineer B4
7Sales Rep3

Expected output (columns: id, path), ordered by id:

1|CEO
2|CEO > VP Eng
3|CEO > VP Sales
4|CEO > VP Eng > Eng Manager
5|CEO > VP Eng > Eng Manager > Engineer A
6|CEO > VP Eng > Eng Manager > Engineer B
7|CEO > VP Sales > Sales Rep

Hint — build the path by string-concatenating (||) the parent's

already-built path with > and the current employee's name.

Sample tests

Test #1Two-branch org chart
Input: "CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);\nINSERT INTO employees VALUES\n (1, 'CEO', NULL),\n (2, 'VP Eng', 1),\n (3, 'VP Sales', 1),\n (4, 'Eng Manager', 2),\n (5, 'Engineer A', 4),\n (6, 'Engineer B', 4),\n (7, 'Sales Rep', 3);"
Output: "1|CEO\n2|CEO > VP Eng\n3|CEO > VP Sales\n4|CEO > VP Eng > Eng Manager\n5|CEO > VP Eng > Eng Manager > Engineer A\n6|CEO > VP Eng > Eng Manager > Engineer B\n7|CEO > VP Sales > Sales Rep"
Test #2Minimal two-level hierarchy
Input: "CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);\nINSERT INTO employees VALUES\n (1, 'Root', NULL),\n (2, 'Child', 1);"
Output: "1|Root\n2|Root > Child"
← All challenges