codejump
MediumSQL

Org Chart Depth Level

SQLDatabasesRecursive CTE

Write a query that returns every employee alongside their depth level
in the org chart: the CEO (no manager) is level 0, their direct reports
are level 1, and so on.

Table: `employees`

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

Expected output (columns: id, name, level), ordered by id:

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

Hint — use WITH RECURSIVE: the base case is the row(s) with

manager_id IS NULL at level 0, the recursive case joins employees

back onto the CTE through manager_id and adds 1 to the level.

Sample tests

Test #1Two-branch org chart, 3 levels deep
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|0\n2|VP Eng|1\n3|VP Sales|1\n4|Eng Manager|2\n5|Engineer A|3\n6|Engineer B|3\n7|Sales Rep|2"
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|0\n2|Child|1"
← All challenges