Direct Report Metrics by Manager

EASY10 pts

An HR analytics warehouse stores organizational data in a single employee table.

Table: employees

employee_id: unique employee identifier
name: employee display name
reports_to: direct manager's employee_id (NULL means the employee has no direct manager)
age: employee age in years

Task: For every manager with at least one direct report, compute:

reports_count: the number of employees who report directly to that manager
average_age: average age of those direct reports, rounded to the nearest integer

Important:

Count only direct reports.
Do not include employees who have zero direct reports.

Output requirements:

Return columns in this exact order:
- employee_id
- name
- reports_count
- average_age
Sort by employee_id in ascending order.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

employees:
| employee_id | name   | reports_to | age |
|-------------|--------|------------|-----|
| 10          | Harper | null       | 46  |
| 21          | Nina   | 10         | 29  |
| 34          | Omar   | 10         | 35  |

Output

[
  {"employee_id":10,"name":"Harper","reports_count":2,"average_age":32}
]

Explanation

Harper has two direct reports (Nina and Omar). Their average age is (29 + 35) / 2 = 32.

Example

Input

employees:
| employee_id | name  | reports_to | age |
|-------------|-------|------------|-----|
| 1           | Mason | null       | 50  |
| 2           | Aria  | 1          | 38  |
| 3           | Liam  | 1          | 44  |
| 4           | Ivy   | 2          | 30  |
| 5           | Noah  | 2          | 35  |
| 6           | Zoe   | 3          | 33  |

Output

[
  {"employee_id":1,"name":"Mason","reports_count":2,"average_age":41},
  {"employee_id":2,"name":"Aria","reports_count":2,"average_age":33},
  {"employee_id":3,"name":"Liam","reports_count":1,"average_age":33}
]

Explanation

Only direct reports are counted. Mason does not directly manage Ivy, Noah, or Zoe.

Example

Input

employees:
| employee_id | name | reports_to | age |
|-------------|------|------------|-----|
| 200         | Ava  | null       | 45  |
| 201         | Ben  | 200        | 30  |
| 202         | Carl | 200        | 31  |
| 300         | Dina | null       | 42  |
| 301         | Eli  | 300        | 22  |
| 302         | Fay  | 300        | 23  |

Output

[
  {"employee_id":200,"name":"Ava","reports_count":2,"average_age":31},
  {"employee_id":300,"name":"Dina","reports_count":2,"average_age":23}
]

Explanation

Ava's team average is 30.5 which rounds to 31, and Dina's team average is 22.5 which rounds to 23.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in employees <= 2000
1 <= employee_id <= 1000000000
employee_id is unique
reports_to is NULL or an existing employee_id in employees
reports_to != employee_id
1 <= length(name) <= 24
name contains only English alphabet characters
16 <= age <= 80
Return exactly columns: employee_id, name, reports_count, average_age
Sort output by employee_id ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with the required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employees =

[{"employee_id":10,"name":"Harper","reports_to":null,"age":46},{"employee_id":21,"name":"Nina","reports_to":10,"age":29},{"employee_id":34,"name":"Omar","reports_to":10,"age":35}]

Direct Report Metrics by Manager

Hints

Direct Report Metrics by Manager

Hints