Skip to main content
Talentd

CEO Subordinate Hierarchy - Solution & Explanation

HardPremiumFree on FleetCodeDatabase5 min read
Practice this problem

Problem Statement

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 subordinate
  • subordinate_name: The name of the subordinate
  • hierarchy_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 salary

Return 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:

  • Bob and Charlie are direct subordinates of Alice (CEO) and thus have a hierarchy_level of 1.
  • David and Eve report to Bob, while Frank and Grace report to Charlie, making them second-level subordinates (hierarchy_level 2).
  • Helen reports to Eve, making Helen a third-level subordinate (hierarchy_level 3).
  • Salary differences are calculated relative to Alice's salary of 150000.
  • The result is ordered by hierarchy_level ascending, and then by subordinate_id ascending.

Note: The output is ordered first by hierarchy_level in ascending order, then by subordinate_id in ascending order.

Approach Overview

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.

Solution

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.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Fixed Depth Self JoinO(n * d)O(1)When hierarchy depth is small and known ahead of time
Iterative Expansion with Temporary TableO(n)O(n)Databases that lack recursive CTE support
Recursive CTE + JoinO(n)O(n)General solution for hierarchical queries in SQL

Video Solution

Leetcode HARD 3236 - RECURSIVE CTE SQL Explained - CEO Subordinate Hierarchy | Everyday Data Science • Everyday Data Science • 1,267 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is CEO Subordinate Hierarchy easy or hard?
CEO Subordinate Hierarchy is considered a hard problem because it requires understanding hierarchical data traversal in SQL. Many developers are comfortable with joins but struggle with recursive CTE patterns needed to query multi-level organizational structures.
CEO Subordinate Hierarchy Python/Java solution
The core problem is SQL-based, so solutions are typically written using recursive CTE queries rather than Python or Java. In application code, the equivalent approach would build an adjacency list and run DFS or BFS to collect all employees under the CEO.
How to solve CEO Subordinate Hierarchy in O(n)?
Start with the CEO row in a recursive CTE. Then repeatedly join the employee table with the CTE using employees.manager_id = hierarchy.employee_id to discover direct subordinates. Each iteration expands the hierarchy until no more rows are found, visiting each employee once for O(n) complexity.
What is the best approach for CEO Subordinate Hierarchy?
The most effective approach uses a recursive CTE combined with a join on the manager relationship. The base query selects the CEO, and the recursive part repeatedly finds employees whose manager_id matches previously discovered employees. This traverses the entire hierarchy in O(n) time and works for any hierarchy depth.
Is CEO Subordinate Hierarchy asked at Google/Amazon/Meta?
Hierarchy traversal problems frequently appear in SQL interviews at large tech companies because they test recursive queries and relational thinking. Variants of employee-manager hierarchy queries have been reported in interviews at companies like Amazon and Google for data engineering and backend roles.
What data structure is used in CEO Subordinate Hierarchy?
The underlying structure is a tree or directed graph where each employee node points to a manager node. Recursive CTE queries simulate traversal of this tree directly inside SQL by expanding parent-child relationships level by level.
What is the time complexity of CEO Subordinate Hierarchy?
Using a recursive CTE, each employee row is processed once while expanding the hierarchy. That results in O(n) time complexity where n is the number of employees in the table. Space complexity is also O(n) because the CTE stores all discovered hierarchy rows.

Ready to solve this problem?

Practice CEO Subordinate Hierarchy with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary