Direct Report Metrics by Manager

Easy

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 1

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 2

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 3

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.

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

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

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 |

[ {"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} ]

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 |