Employee Directory - Build Org Chart with Reporting Chain Queries

Question

## Problem

You have an employee dataset: `[(emp_id, name, manager_id)]`. The root employee has `manager_id = None`.

Implement:

```python
class OrgChart:
    def __init__(self, employees: list[tuple[int, str, int | None]]): ...
    def manager_chain(self, emp_id: int) -> list[str]:
        # names from direct manager up to CEO, inclusive
    def direct_reports(self, emp_id: int) -> list[str]:
        # sorted alphabetically
    def department_size(self, emp_id: int) -> int:
        # count all reports recursively, including emp_id
```

## Example

```
employees = [(1,"CEO",None),(2,"VP",1),(3,"Eng",2),(4,"PM",2)]
chart.manager_chain(3)    -> ["VP", "CEO"]
chart.direct_reports(2)   -> ["Eng", "PM"]
chart.department_size(2)  -> 3
```

## Follow-ups

1. How do you detect cycles in the manager relationship (corrupt data)?
2. Write `lowest_common_manager(a, b)` that returns the closest shared ancestor.
3. How would you write `department_size` as a SQL recursive CTE?
4. What changes if an employee can have multiple managers (matrix org)?

Employee Directory - Build Org Chart with Reporting Chain Queries

Question Details

Full Details

Topics

More from Amazon