Report Direct-Report Counts and Rounded Ages for Every Manager
Company: Google
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Other
### Problem
A manager is an employee with at least one other employee reporting directly to them. Return employee_id, name, reports_count and average_age for every manager. Average only their direct reports’ ages, rounded to the nearest integer (round .5 up), and sort employee_id ascending. All ages are known nonnegative integers.
### Tables
Table: `Employees`
```text
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| employee_id | int |
| name | varchar |
| reports_to | int |
| age | int |
+-------------+----------+
employee_id is the column with unique values for this table.
This table contains information about the employees and the id of the manager they report to. Some employees do not report to anyone (reports_to is null).
```
### Example
```text
Employees (employee_id,name,reports_to,age): (1,Ada,NULL,50), (2,Bo,1,30), (3,Cam,1,35), (4,Dee,2,20), (5,Eli,NULL,44)
Output (employee_id,name,reports_count,average_age): (1,Ada,2,33), (2,Bo,1,20)
```
### What a Strong Answer Covers
- Count only direct reports for each manager.
- Average the ages of those same direct reports and order the output by employee ID.
### Follow-up Questions
- How would the results change if all descendants counted as reports?
- How would you include managers whose direct reports have missing ages?
Overview: Return each manager's direct-report count and their direct reports' rounded average age.