# Chain of Command

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

Canonical URL: <https://datadriven.io/problems/chain_of_command>

Domain: SQL · Difficulty: hard · Seniority: L5

## 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.

## 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.

> **Trick to Solving**
>
> Recursive CTEs follow a template: anchor (root nodes), UNION ALL, recursive step (self-join). Recursion terminates when no new rows are produced.
> 
> 1. Write the anchor: SELECT root employees (WHERE manager_id IS NULL)
> 2. Write the recursive step: JOIN CTE to table on parent-child link
> 3. Add a level counter for depth tracking

---

### 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**

```sql
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
```

> **Cost Analysis**
>
> One pass per hierarchy level. For a balanced tree with N nodes, total cost is O(N). Deep hierarchies (100+ levels) may hit recursion limits.

> **Interviewers Watch For**
>
> The interviewer checks: correct anchor, correct join direction, and cycle detection awareness.

> **Common Pitfall**
>
> Joining `t.manager_id = e.employee_id` instead of `e.manager_id = t.employee_id` traverses up instead of down, so each employee appears once at their own level and never gathers its descendants.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/chain_of_command)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.