# Flatten Org Chart Hierarchy

> The tree runs deep. Walk every branch to the root.

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

Each employee has a manager_id pointing to their manager's employee_id (NULL for the CEO). Return every employee with their depth in the tree and the full path from CEO down, formatted like a file path with / between names. Results should follow path order.

## Worked solution and explanation

### Why this problem exists in real interviews

Flattening a hierarchy tests **recursive CTEs**, one of the most advanced SQL patterns. The interviewer probes whether you understand the anchor, recursive step, and termination condition.

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

Select employees where `manager_id IS NULL`. These are the top of the hierarchy at level 0.

#### Step 2: Define the recursive step

Join `employees` to the CTE where `manager_id = employee_id` to find direct reports. Increment the level.

#### Step 3: Select the flattened result

The CTE output gives all employees with their hierarchy depth.

---

### The solution

**Recursive CTE for hierarchical traversal**

```sql
WITH RECURSIVE org_tree AS (
    SELECT employee_id, emp_name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.emp_name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT employee_id, emp_name, manager_id, level
FROM org_tree
ORDER BY level, emp_name
```

> **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 `ot.manager_id = e.employee_id` instead of `e.manager_id = ot.employee_id` traverses up instead of down.

---

## 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/flatten_org_chart_hierarchy)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.