CEO Subordinate Hierarchy - Problem

Given an Employees table with employee information including their manager relationships, write a SQL query to find all subordinates of the CEO (both direct and indirect).

The CEO is identified as the employee with manager_id = NULL.

Requirements:

  • subordinate_id: The employee_id of the subordinate
  • subordinate_name: The name of the subordinate
  • hierarchy_level: The level in hierarchy (1 for direct reports, 2 for their reports, etc.)
  • salary_difference: Difference between subordinate's salary and CEO's salary

Return results ordered by hierarchy_level ascending, then by subordinate_id ascending.

Table Schema

Employees
Column Name Type Description
employee_id PK int Unique identifier for each employee
employee_name varchar Name of the employee
manager_id int Employee ID of the manager (NULL for CEO)
salary int Employee's salary
Primary Key: employee_id
Note: The CEO has manager_id = NULL. Other employees have manager_id pointing to their manager's employee_id.

Input & Output

Example 1 — Multi-level Hierarchy
Input Table:
employee_id employee_name manager_id salary
1 Alice 150000
2 Bob 1 120000
3 Charlie 1 110000
4 David 2 105000
5 Eve 2 100000
6 Frank 3 95000
7 Grace 3 98000
8 Helen 5 90000
Output:
subordinate_id subordinate_name hierarchy_level salary_difference
2 Bob 1 -30000
3 Charlie 1 -40000
4 David 2 -45000
5 Eve 2 -50000
6 Frank 2 -55000
7 Grace 2 -52000
8 Helen 3 -60000
💡 Note:

Alice is the CEO (manager_id = NULL). Bob and Charlie report directly to Alice (level 1). David and Eve report to Bob, Frank and Grace report to Charlie (level 2). Helen reports to Eve (level 3). All salary differences are calculated relative to Alice's salary of 150000.
Example 2 — Simple Two-level Hierarchy
Input Table:
employee_id employee_name manager_id salary
1 CEO 200000
2 Manager1 1 120000
3 Manager2 1 130000
Output:
subordinate_id subordinate_name hierarchy_level salary_difference
2 Manager1 1 -80000
3 Manager2 1 -70000
💡 Note:

Simple case with CEO having two direct reports. Both managers are at hierarchy level 1 with negative salary differences compared to the CEO's 200000 salary.
Example 3 — Single Employee Company
Input Table:
employee_id employee_name manager_id salary
1 OnlyEmployee 100000
Output:
subordinate_id subordinate_name hierarchy_level salary_difference
💡 Note:

Edge case where there's only one employee who is the CEO. Since they have no subordinates, the result is empty.

Constraints

  • 1 ≤ employee_id ≤ 2000
  • 1 ≤ employee_name.length ≤ 15
  • 1 ≤ salary ≤ 10^6
  • There is exactly one CEO (employee with manager_id = NULL)
  • All manager_id values (except CEO) reference valid employee_id values

Visualization

Tap to expand
CEO Subordinate Hierarchy ProblemInput: Employeesidnamemgrsalary1AliceNULL1500002Bob11200003Charlie1110000AliceCEOLevel 0BobCharlieHierarchy TreeRecursive CTEWITH RECURSIVEOutput: Subordinatessub_idnamelevelsal_diff2Bob1-300003Charlie1-40000
Understanding the Visualization
1
Input
Employee table with manager relationships
2
Recursive CTE
Build hierarchy tree from CEO down
3
Output
All subordinates with levels and salary differences
Key Takeaway
🎯 Key Insight: Use recursive CTEs to efficiently traverse hierarchical data structures like organizational charts

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15 Meta 12
28.4K Views
Medium Frequency
~18 min Avg. Time
892 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