All People Report to the Given Manager - Problem

You are given an Employees table representing the company hierarchy. Each employee has an ID, name, and their direct manager's ID.

Write a SQL solution to find all employees that directly or indirectly report to the head of the company (employee_id = 1).

Key Points:

  • Direct reporting: Employee → Manager
  • Indirect reporting: Employee → Manager → Manager's Manager (up to 3 levels)
  • The head of company has employee_id = 1
  • Maximum hierarchy depth is 3 managers

Table Schema

Employees
Column Name Type Description
employee_id PK int Unique employee identifier
employee_name varchar Employee's full name
manager_id int ID of direct manager (NULL for head)
Primary Key: employee_id
Note: employee_id = 1 is the head of company. manager_id can be NULL for the head.

Input & Output

Example 1 — Complete Hierarchy
Input Table:
employee_id employee_name manager_id
1 Boss
2 Alice 1
3 Bob 2
4 Charlie 2
5 David 3
Output:
employee_id
2
3
4
5
💡 Note:

Boss (ID=1) is the head. Alice (ID=2) reports directly to Boss. Bob and Charlie (ID=3,4) report to Alice, so indirectly to Boss. David (ID=5) reports to Bob, so indirectly to Boss through 2 levels.
Example 2 — Only Direct Reports
Input Table:
employee_id employee_name manager_id
1 Boss
2 Alice 1
3 Bob 1
Output:
employee_id
2
3
💡 Note:

Simple case where Alice and Bob both report directly to the head (Boss). No indirect reporting levels exist.
Example 3 — Separate Branch
Input Table:
employee_id employee_name manager_id
1 Boss
2 Alice 1
7 Eve 6
6 Frank
Output:
employee_id
2
💡 Note:

Only Alice reports to Boss (ID=1). Eve reports to Frank (ID=6) who is a separate head, so Eve is not included in the results.

Constraints

  • 1 ≤ employee_id ≤ 2000
  • employee_id is unique for each employee
  • manager_id is NULL only for company heads
  • Hierarchy depth will not exceed 3 levels below head

Visualization

Tap to expand
Company Reporting Structure AnalysisEmployees Tableemp_idnamemgr_id1BossNULL2Alice13Bob25David3Self-Join HierarchyReports to Head (ID=1)employee_id235Reporting Chain VisualizationBoss (1)Alice (2)Bob (3)David (5)Direct ReportIndirect Report (Level 2)Indirect Report (Level 3)
Understanding the Visualization
1
Input Table
Employee records with manager relationships
2
Self-Join
Connect employees through hierarchy levels
3
Filter Results
Find all reporting to head (ID=1)
Key Takeaway
🎯 Key Insight: Self-joins enable traversing hierarchical relationships by connecting a table to itself through foreign key relationships

Related Problems

Asked in
Facebook 28 Amazon 22 Microsoft 15
28.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen