Executive Reporting Depth and Compensation Delta

Hard

An HR analytics team needs a hierarchy view that combines reporting depth and compensation gap versus the CEO. Table: Employees(employee_id, employee_name, manager_id, salary) Definitions: Exactly one row is the CEO and has manager_id = NULL. Every other employee belongs to the CEO's reporting tree. hierarchy_level is the number of manager hops from CEO to subordinate. salary_difference is subordinate salary minus CEO salary. Task: Return one row per subordinate of the CEO (direct and indirect). Output columns (exact order): subordinate_id subordinate_name hierarchy_level salary_difference Final ordering: hierarchy_level ascending subordinate_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

employees:
| employee_id | employee_name | manager_id | salary |
|-------------|---------------|------------|--------|
| 10          | Ava           | NULL       | 150000 |
| 12          | Ben           | 10         | 120000 |
| 13          | Cara          | 10         | 112000 |
| 20          | Drew          | 12         | 105000 |
| 21          | Eli           | 12         | 100000 |
| 30          | Fern          | 13         | 98000  |
| 40          | Gray          | 21         | 90000  |

Output

[
  {"subordinate_id":12,"subordinate_name":"Ben","hierarchy_level":1,"salary_difference":-30000},
  {"subordinate_id":13,"subordinate_name":"Cara","hierarchy_level":1,"salary_difference":-38000},
  {"subordinate_id":20,"subordinate_name":"Drew","hierarchy_level":2,"salary_difference":-45000},
  {"subordinate_id":21,"subordinate_name":"Eli","hierarchy_level":2,"salary_difference":-50000},
  {"subordinate_id":30,"subordinate_name":"Fern","hierarchy_level":2,"salary_difference":-52000},
  {"subordinate_id":40,"subordinate_name":"Gray","hierarchy_level":3,"salary_difference":-60000}
]

Explanation

Depth is measured from the CEO and salary deltas are relative to CEO compensation.

Example 2

Input

employees:
| employee_id | employee_name | manager_id | salary |
|-------------|---------------|------------|--------|
| 500         | Noor          | NULL       | 100000 |
| 110         | Ivan          | 500        | 100000 |
| 120         | Jia           | 500        | 140000 |
| 121         | Kai           | 120        | 150000 |

Output

[
  {"subordinate_id":110,"subordinate_name":"Ivan","hierarchy_level":1,"salary_difference":0},
  {"subordinate_id":120,"subordinate_name":"Jia","hierarchy_level":1,"salary_difference":40000},
  {"subordinate_id":121,"subordinate_name":"Kai","hierarchy_level":2,"salary_difference":50000}
]

Explanation

Differences can be negative, zero, or positive depending on subordinate salary.

Example 3

Input

employees:
| employee_id | employee_name | manager_id | salary |
|-------------|---------------|------------|--------|
| 7           | Uma           | NULL       | 180000 |

Output

[]

Explanation

If only the CEO exists, there are no subordinate rows to return.

Constraints

1 <= number of rows in employees <= 200000
employee_id is unique
Exactly one row has manager_id = NULL
Each non-CEO manager_id references an existing employee_id
The reporting relation forms a valid rooted hierarchy (no cycles)
0 <= salary <= 1000000000
Output must include only CEO descendants (exclude CEO)
hierarchy_level starts at 1 for direct CEO reports
salary_difference = subordinate_salary - ceo_salary
Sort by hierarchy_level ASC, subordinate_id ASC
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns [subordinate_id, subordinate_name, hierarchy_level, salary_difference]

Preparing your editor. You can read the problem while it loads.

Executive Reporting Depth and Compensation Delta

Hard

An HR analytics team needs a hierarchy view that combines reporting depth and compensation gap versus the CEO. Table: Employees(employee_id, employee_name, manager_id, salary) Definitions: Exactly one row is the CEO and has manager_id = NULL. Every other employee belongs to the CEO's reporting tree. hierarchy_level is the number of manager hops from CEO to subordinate. salary_difference is subordinate salary minus CEO salary. Task: Return one row per subordinate of the CEO (direct and indirect). Output columns (exact order): subordinate_id subordinate_name hierarchy_level salary_difference Final ordering: hierarchy_level ascending subordinate_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

employees:
| employee_id | employee_name | manager_id | salary |
|-------------|---------------|------------|--------|
| 10          | Ava           | NULL       | 150000 |
| 12          | Ben           | 10         | 120000 |
| 13          | Cara          | 10         | 112000 |
| 20          | Drew          | 12         | 105000 |
| 21          | Eli           | 12         | 100000 |
| 30          | Fern          | 13         | 98000  |
| 40          | Gray          | 21         | 90000  |

Output

[
  {"subordinate_id":12,"subordinate_name":"Ben","hierarchy_level":1,"salary_difference":-30000},
  {"subordinate_id":13,"subordinate_name":"Cara","hierarchy_level":1,"salary_difference":-38000},
  {"subordinate_id":20,"subordinate_name":"Drew","hierarchy_level":2,"salary_difference":-45000},
  {"subordinate_id":21,"subordinate_name":"Eli","hierarchy_level":2,"salary_difference":-50000},
  {"subordinate_id":30,"subordinate_name":"Fern","hierarchy_level":2,"salary_difference":-52000},
  {"subordinate_id":40,"subordinate_name":"Gray","hierarchy_level":3,"salary_difference":-60000}
]

Explanation

Depth is measured from the CEO and salary deltas are relative to CEO compensation.

Example 2

Input

employees:
| employee_id | employee_name | manager_id | salary |
|-------------|---------------|------------|--------|
| 500         | Noor          | NULL       | 100000 |
| 110         | Ivan          | 500        | 100000 |
| 120         | Jia           | 500        | 140000 |
| 121         | Kai           | 120        | 150000 |

Output

[
  {"subordinate_id":110,"subordinate_name":"Ivan","hierarchy_level":1,"salary_difference":0},
  {"subordinate_id":120,"subordinate_name":"Jia","hierarchy_level":1,"salary_difference":40000},
  {"subordinate_id":121,"subordinate_name":"Kai","hierarchy_level":2,"salary_difference":50000}
]

Explanation

Differences can be negative, zero, or positive depending on subordinate salary.

Example 3

Input

employees:
| employee_id | employee_name | manager_id | salary |
|-------------|---------------|------------|--------|
| 7           | Uma           | NULL       | 180000 |

Output

[]

Explanation

If only the CEO exists, there are no subordinate rows to return.

Constraints

1 <= number of rows in employees <= 200000
employee_id is unique
Exactly one row has manager_id = NULL
Each non-CEO manager_id references an existing employee_id
The reporting relation forms a valid rooted hierarchy (no cycles)
0 <= salary <= 1000000000
Output must include only CEO descendants (exclude CEO)
hierarchy_level starts at 1 for direct CEO reports
salary_difference = subordinate_salary - ceo_salary
Sort by hierarchy_level ASC, subordinate_id ASC
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns [subordinate_id, subordinate_name, hierarchy_level, salary_difference]