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.
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.
Leetcode Interviews • ThePrimeTime • 1,196,262 views views
Watch 9 more video solutions →Practice CEO Subordinate Hierarchy with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor