Direct Reports Tree - Find All Reports Under a Manager

Question

## Problem

You are given an employee table with `(employee_id, name, manager_id)`. Write a function that returns all direct and indirect reports of a given manager, at any depth.

```python
def all_reports(employees: List[dict], manager_id: int) -> List[int]:
    # employees: [{"id": int, "name": str, "manager_id": int | None}]
    # returns list of employee IDs reporting under manager_id (inclusive of all levels)
    ...
```

**Example:**
```
employees = [
  {"id": 1, "name": "Alice", "manager_id": None},
  {"id": 2, "name": "Bob",   "manager_id": 1},
  {"id": 3, "name": "Carol", "manager_id": 1},
  {"id": 4, "name": "Dave",  "manager_id": 2},
  {"id": 5, "name": "Eve",   "manager_id": 2}
]
all_reports(employees, 1) -> [2, 3, 4, 5]
all_reports(employees, 2) -> [4, 5]
```

## Follow-ups

1. How would you express this as a recursive CTE in SQL?
2. What is the time complexity if the org chart has N employees and depth D?
3. How would you detect cycles (invalid data where A reports to B and B reports to A)?
4. Extend to return the full subtree as a nested dict.

Direct Reports Tree - Find All Reports Under a Manager

Question Details

Full Details

Topics

More from Rippling