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

Loading problem...

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

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