Chain of Command

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: hard
Seniority: L5
Asked in: Visa

Problem

Every employee's manager_id points to their manager's employee_id, except the CEO, whose manager_id is NULL. Return each employee with their depth beneath the CEO and the full chain of names from the CEO down, joined by / like a file path and listed in path order.

The tree runs deep. Trace every branch back to the top.

Tables

employees

Columns: employee_id INTEGER, emp_name TEXT, manager_id INTEGER, department TEXT, salary REAL, hire_date TEXT, user_id INTEGER

employees: 10 of 200 rows
employee_id	emp_name	department	salary	manager_id	hire_date	user_id
1	Alice Chen	Engineering	55731	NULL	2021-02-02	100
2	Bob Martinez	Product	61462	NULL	2022-03-03	197
3	Carol Wu	Design	67193	NULL	2023-04-04	294
4	David Kim	Marketing	72924	NULL	2024-05-05	391
5	Eve Johnson	Sales	78655	NULL	2025-06-06	488
6	Frank Patel	HR	84386	NULL	2026-07-07	585
7	Grace Lee	Finance	55117	NULL	2020-08-08	682
8	Hank Brown	Operations	60848	NULL	2021-09-09	779
9	Iris Davis	Data	66579	NULL	2022-10-10	876
10	Jake Wilson	Legal	72310	NULL	2023-11-11	973

Expected output

First 5 of 200 rows
employee_id	emp_name	depth	path
1	Alice Chen	0	Alice Chen
10000101	Alice Chen	0	Alice Chen
30	Dan Baker	1	Alice Chen/Dan Baker
10000130	Dan Baker	1	Alice Chen/Dan Baker
2	Bob Martinez	0	Bob Martinez

Worked solution and explanation

What this really is

This is a hierarchy walk wearing an org-chart costume. The real skill: a recursive CTE that anchors on the roots (managers with no manager), then joins downward one level at a time, building each person's path as it goes. The trap almost everyone hits is the join direction. Write t.manager_id = e.employee_id and you climb up toward the CEO instead of descending from it, so each person surfaces once at their own level and never carries their subtree's path. Get it backwards on cyclic data and the recursion never terminates.

Break down the requirements

Step 1: Define the anchor (root nodes)

Anchor the recursive CTE on the CEO (manager_id IS NULL), seeding depth=0 and path=emp_name.

Step 2: Define the recursive step

Recursively join employees onto the tree by e.manager_id = t.employee_id, incrementing depth and appending '/' || emp_name to build the file-path-style chain from the CEO down. Note the direction: the NEW row's manager_id matches the ALREADY-visited parent's employee_id.

Step 3: Select the flattened result

Select employee_id, emp_name, depth, and path from the assembled tree, ordered by path so siblings and their subtrees stay contiguous.

The solution

Recursive CTE for hierarchical traversal

WITH RECURSIVE tree AS (
    SELECT employee_id, emp_name, manager_id, 0 AS depth, emp_name AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.emp_name, e.manager_id, t.depth + 1, t.path || '/' || e.emp_name
    FROM employees e
    JOIN tree t ON e.manager_id = t.employee_id
)
SELECT employee_id, emp_name, depth, path
FROM tree
ORDER BY path, employee_id

Common follow-up questions

How would you detect cycles in the hierarchy? (Tests adding a path array and checking for repeated IDs.)
How would you find the depth of the deepest branch? (Tests MAX(level) on the CTE output.)
How would you compute headcount under each manager? (Tests aggregating the CTE result by manager.)
How would you limit recursion depth? (Tests WHERE level < `max_depth` in the recursive member.)