00:00:00

Description

Editorial

Executive Reporting Depth and Compensation Delta

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employees =

[{"employee_id":10,"employee_name":"Ava","manager_id":null,"salary":150000},{"employee_id":12,"employee_name":"Ben","manager_id":10,"salary":120000},{"employee_id":13,"employee_name":"Cara","manager_id":10,"salary":112000},{"employee_id":20,"employee_name":"Drew","manager_id":12,"salary":105000},{"employee_id":21,"employee_name":"Eli","manager_id":12,"salary":100000},{"employee_id":30,"employee_name":"Fern","manager_id":13,"salary":98000},{"employee_id":40,"employee_name":"Gray","manager_id":21,"salary":90000}]

Loading problem...

101

00:00:00

Description

Editorial

Executive Reporting Depth and Compensation Delta

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employees =

Executive Reporting Depth and Compensation Delta

Hints

Executive Reporting Depth and Compensation Delta

Hints