Table: Employees
+---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | employee_name | varchar | | manager_id | int | | salary | int | +---------------+---------+ employee_id is the unique identifier for this table. manager_id is the employee_id of the employee's manager. The CEO has a NULL manager_id.
Write a solution to find subordinates of the CEO (both direct and indirect), along with their level in the hierarchy and their salary difference from the CEO.
The result should have the following columns:
The query result format is in the following example.
subordinate_id: The employee_id of the subordinatesubordinate_name: The name of the subordinatehierarchy_level: The level of the subordinate in the hierarchy (1 for direct reports, 2 for their direct reports, and so on)salary_difference: The difference between the subordinate's salary and the CEO's salaryReturn the result table ordered by hierarchy_level ascending, and then by subordinate_id ascending.
The query result format is in the following example.
Example:
Input:
Employees table:
+-------------+----------------+------------+---------+ | employee_id | employee_name | manager_id | salary | +-------------+----------------+------------+---------+ | 1 | Alice | NULL | 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 | +----------------+------------------+------------------+-------------------+
Explanation:
Note: The output is ordered first by hierarchy_level in ascending order, then by subordinate_id in ascending order.
Problem Overview: The table represents a company org chart where each employee may report to a manager. The task is to return the full hierarchy of employees under the CEO, including indirect subordinates across multiple reporting levels.
Approach 1: Fixed Depth Self Join (O(n * d) time, O(1) space)
A straightforward SQL attempt uses repeated JOIN operations between the employee table and itself. Each join expands one level deeper in the hierarchy: employee → manager → manager's manager, and so on. This works only when the maximum depth d of the hierarchy is known ahead of time. In real organizations the depth can vary, so queries quickly become fragile and difficult to maintain. Each additional level requires another join and increases query complexity.
Approach 2: Iterative Expansion with Temporary Table (O(n) time, O(n) space)
Another option inserts the CEO into a temporary result table, then repeatedly finds employees whose manager_id matches any employee already discovered. Each iteration appends new subordinates until no more rows are found. This approach models a breadth‑first traversal similar to exploring a tree or graph. It works in SQL engines without recursion support, but requires multiple statements or procedural loops, which makes the logic harder to embed in a single query.
Approach 3: Recursive CTE + Join (O(n) time, O(n) space)
The most robust solution uses a recursive Common Table Expression. The base query selects the CEO row. The recursive part joins the employee table with the previous CTE result using employees.manager_id = hierarchy.employee_id. Each iteration discovers the next level of subordinates. The recursion stops automatically when no additional matches exist.
This pattern effectively performs a depth‑first traversal of the organization tree using SQL. Every employee record is visited once, giving O(n) time complexity where n is the number of employees. The CTE stores intermediate rows, resulting in O(n) space complexity. Recursive CTEs are widely supported in modern SQL engines and are the standard solution for hierarchical queries in database problems.
Recommended for interviews: The recursive CTE approach is the expected solution. It handles arbitrary hierarchy depth, expresses the traversal in a single query, and scales well with large datasets. Mentioning the fixed self‑join method shows you understand the structure of the hierarchy, but using recursion demonstrates practical SQL knowledge and familiarity with hierarchical data traversal.
First, we use a recursive CTE to calculate the hierarchy level of each employee, where the CEO's level is 0. We save employee_id, employee_name, hierarchy_level, manager_id, and salary into a temporary table T.
Then, we query the CEO's salary and save it into a temporary table P.
Finally, we join tables T and P to calculate the salary difference for each subordinate, and sort by hierarchy_level and subordinate_id.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Fixed Depth Self Join | O(n * d) | O(1) | When hierarchy depth is small and known ahead of time |
| Iterative Expansion with Temporary Table | O(n) | O(n) | Databases that lack recursive CTE support |
| Recursive CTE + Join | O(n) | O(n) | General solution for hierarchical queries in SQL |
Leetcode HARD 3236 - RECURSIVE CTE SQL Explained - CEO Subordinate Hierarchy | Everyday Data Science • Everyday Data Science • 1,149 views views
Watch 2 more video solutions →Practice CEO Subordinate Hierarchy with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor