Skip to content

3236. CEO Subordinate Hierarchy 👍

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
WITH RECURSIVE
  EmployeeHierarchy AS (
    -- Base case: direct reports to CEO
    SELECT
      employee_id,
      employee_name,
      salary,
      1 AS hierarchy_level
    FROM Employees
    WHERE manager_id = (
        SELECT employee_id
        FROM Employees
        WHERE manager_id IS NULL
      )
    UNION ALL
    -- Recursive case: reports of reports
    SELECT
      Employees.employee_id,
      Employees.employee_name,
      Employees.salary,
      EmployeeHierarchy.hierarchy_level + 1
    FROM Employees
    INNER JOIN EmployeeHierarchy
      ON (Employees.manager_id = EmployeeHierarchy.employee_id)
  ),
  Ceo AS (
    SELECT salary
    FROM Employees
    WHERE manager_id IS NULL
  )
SELECT
  EmployeeHierarchy.employee_id AS subordinate_id,
  EmployeeHierarchy.employee_name AS subordinate_name,
  EmployeeHierarchy.hierarchy_level,
  EmployeeHierarchy.salary - Ceo.salary AS salary_difference
FROM EmployeeHierarchy
CROSS JOIN Ceo
ORDER BY hierarchy_level, employee_id;