Skip to main content
Talentd

All People Report to the Given Manager - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon, Google
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

We can use two joins to find all employees who report directly or indirectly to the company CEO.

Specifically, we first use a join to find the manager_id of the superior manager for each manager_id, and then use another join to find the manager_id of the higher-level manager. Finally, if the manager_id of the higher-level manager is 1 and the employee_id of the employee is not 1, it means that the employee reports directly or indirectly to the company CEO.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen 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 INO(n)O(1)Readable alternative when filtering employees whose managers match a specific condition.

Video Solution

LeetCode Medium 1270 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 6,482 views views

Watch 6 more video solutions →

Frequently Asked Questions

Is All People Report to the Given Manager easy or hard?
The problem is rated Medium because it requires understanding hierarchical relationships and self joins in SQL. Developers familiar with join operations and table aliasing usually solve it quickly, while beginners may struggle with chaining joins correctly.
All People Report to the Given Manager Python/Java solution
This problem is a database query problem, so the primary solution is written in SQL (MySQL). Python or Java would typically execute the SQL query using a database connector rather than implementing the logic directly in application code.
How to solve All People Report to the Given Manager in O(n)?
Use a self-join query that links employees to their managers and then links those managers to the given manager (id = 1). The join chain validates the reporting relationship directly inside the query. Since each row is processed once during joins, the overall complexity is O(n).
What is the best approach for All People Report to the Given Manager?
The most efficient approach uses SQL self joins. Join the Employees table to itself to follow the reporting chain from employee to manager and then to the target manager. This two-join strategy resolves the hierarchy in a single query with O(n) time complexity.
Is All People Report to the Given Manager asked at Google/Amazon/Meta?
Hierarchy and organizational reporting queries are common SQL interview topics at companies like Amazon, Google, and Meta. Variations of this problem appear when testing knowledge of joins, self joins, and hierarchical data modeling in relational databases.
What data structure is used in All People Report to the Given Manager?
The problem relies on a relational table that represents a hierarchy using a parent reference (manager_id). In SQL terms, the solution uses self joins on the Employees table to traverse the hierarchy and identify reporting relationships.
What is the time complexity of All People Report to the Given Manager?
The SQL solution using two self joins runs in O(n) time where n is the number of rows in the Employees table. Database indexes on employee_id and manager_id can further improve performance during join operations.

Ready to solve this problem?

Practice All People Report to the Given Manager with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary