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 INPUT: Employees Table id name mgr_id salary 1 Alice NULL 150000 2 Bob 1 120000 3 Carol 1 110000 4 David 2 90000 5 Eve 2 85000 6 Frank 3 80000 Hierarchy Tree Alice (CEO) Bob Carol David Eve Frank ALGORITHM: Recursive CTE 1 Find CEO (Base Case) SELECT * FROM Employees WHERE manager_id IS NULL 2 Recursive Part Join Employees with CTE WHERE e.manager_id = cte.id Increment level each step 3 Calculate Salary Diff CEO salary stored in CTE Subtract from each subordinate 4 Filter and Order Exclude CEO (level > 0) ORDER BY level, sub_id WITH RECURSIVE sub AS ( SELECT id, name, salary, 0 AS lvl, salary AS ceo_sal FROM Employees WHERE manager_id IS NULL UNION ALL ... FINAL RESULT sub_id name lvl sal_diff 2 Bob 1 -30000 3 Carol 1 -40000 4 David 2 -60000 5 Eve 2 -65000 6 Frank 2 -70000 Level Breakdown Level 1: Direct reports to CEO (Bob, Carol) Level 2: Reports to Level 1 Query Complete - OK 5 subordinates found Ordered by level, then ID Key Insight: Recursive CTE for Hierarchical Data Recursive CTEs are ideal for traversing tree structures like org charts. The base case finds the root (CEO), and the recursive part joins employees with their managers. Each recursion increments the hierarchy level. Storing the CEO salary in the CTE allows calculating salary differences without additional subqueries. TutorialsPoint - CEO Subordinate Hierarchy | Optimal Solution (Recursive CTE)

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