Watch 7 video solutions for All People Report to the Given Manager, a medium level problem involving Database. This walkthrough by Everyday Data Science has 6,138 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Employees
+---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | employee_name | varchar | | manager_id | int | +---------------+---------+ employee_id is the column of unique values for this table. Each row of this table indicates that the employee with ID employee_id and name employee_name reports his work to his/her direct manager with manager_id The head of the company is the employee with employee_id = 1.
Write a solution to find employee_id of all employees that directly or indirectly report their work to the head of the company.
The indirect relation between managers will not exceed three managers as the company is small.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Employees table: +-------------+---------------+------------+ | employee_id | employee_name | manager_id | +-------------+---------------+------------+ | 1 | Boss | 1 | | 3 | Alice | 3 | | 2 | Bob | 1 | | 4 | Daniel | 2 | | 7 | Luis | 4 | | 8 | Jhon | 3 | | 9 | Angela | 8 | | 77 | Robert | 1 | +-------------+---------------+------------+ Output: +-------------+ | employee_id | +-------------+ | 2 | | 77 | | 4 | | 7 | +-------------+ Explanation: The head of the company is the employee with employee_id 1. The employees with employee_id 2 and 77 report their work directly to the head of the company. The employee with employee_id 4 reports their work indirectly to the head of the company 4 --> 2 --> 1. The employee with employee_id 7 reports their work indirectly to the head of the company 7 --> 4 --> 2 --> 1. The employees with employee_id 3, 8, and 9 do not report their work to the head of the company directly or indirectly.
Problem Overview: The table Employees stores an organizational hierarchy using employee_id and manager_id. The task is to return all employees who ultimately report to a specific manager (manager id = 1) through the reporting chain.
Approach 1: Two Joins (Self Join Hierarchy Traversal) (Time: O(n), Space: O(1))
This approach walks the reporting hierarchy using SQL self joins. Each row in Employees links an employee to their direct manager via manager_id. By joining the table to itself twice, you can follow the chain employee → manager → given manager. The first join connects an employee to their manager, and the second join connects that manager to the top-level manager (id = 1).
The key insight is that organizational hierarchies are naturally represented as parent-child relationships. A self join lets you traverse those relationships directly inside SQL without recursion. Once the join chain confirms the top manager, filter out the manager record itself and return the employee ids.
This pattern is common when querying hierarchical data in relational databases. It appears frequently in interview-style SQL problems involving reporting structures, org charts, or category trees. Understanding how to chain self joins is essential when working with database problems that store relationships in a single table.
When the hierarchy depth is known or limited, a fixed number of joins is efficient and easy to read. For deeper or unknown hierarchies, recursive queries or CTEs are usually preferred. However, in this problem a simple self-join chain solves it cleanly using standard SQL operations and joins.
Recommended for interviews: The two self-join approach is the expected solution. It demonstrates that you understand hierarchical relationships in relational tables and can traverse them using joins. Simpler brute-force approaches or repeated queries show the idea, but the self-join solution proves solid SQL fundamentals.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Two Self Joins (Hierarchy Traversal) | O(n) | O(1) | Best when hierarchy depth is known and small. Efficient for manager → employee relationship queries. |
| Subquery with IN | O(n) | O(1) | Readable alternative when filtering employees whose managers match a specific condition. |
