The Number of Employees Which Report to Each Employee

Difficulty: Easy | Acceptance: 53.30% | Paid: No Topics: Database

Table: Employees

+-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | | reports_to | int | | age | int | +-------------+---------+ employee_id is the primary key for this table. This table contains information about the employees and the ID of the manager they report to (reports_to). Some employees do not report to anyone (reports_to is null).

Write an SQL query to report the ids and the names of all the employees that do not report to anyone, and the number of employees that report to each of them, rounded to the nearest integer.

Return the result table ordered by employee_id.

The query result format is in the following example.

• Examples
• Constraints
• Approach 1: Self-Join with Group By
• Approach 2: Subquery with Aggregation

Examples

Example 1

Input:

Employees table:
+-------------+---------------+------------+-----+
| employee_id | name          | reports_to | age |
+-------------+---------------+------------+-----+
| 9           | Hercy         | null       | 43  |
| 6           | Alice         | 9          | 41  |
| 4           | Bob           | 9          | 36  |
| 2           | Winston       | null       | 37  |
+-------------+---------------+------------+-----+

Output:

+-------------+---------------+--------------+-------------+
| employee_id | name          | reports_count| average_age |
+-------------+---------------+--------------+-------------+
| 2           | Winston       | 0            | null        |
| 9           | Hercy         | 2            | 39          |
+-------------+---------------+--------------+-------------+

Explanation:

• Hercy has 2 people report to him: Alice and Bob. Their average age is (41 + 36) / 2 = 38.5, which rounds to 39.
• Winston has no one report to him.
• The result table is ordered by employee_id.

Constraints

1 <= employee_id <= 1000
name is a non-empty string
age is a positive integer
reports_to is either null or a valid employee_id

Approach 1: Self-Join with Group By

Intuition Join the Employees table with itself on the condition that the manager’s employee_id matches the reports_to column, then filter for managers only and group by manager to count reports and calculate average age.

Steps

• Perform a LEFT JOIN of Employees with itself, matching employee_id with reports_to
• Filter for employees where reports_to is null (managers only)
• Group by the manager’s employee_id and name
• Count the number of reports using COUNT on employee_id
• Calculate the average age using AVG on the employee’s age
• Round the average age to the nearest integer
• Order the result by employee_id

import pandas as pd
import numpy as np

def count_employees(employees: pd.DataFrame) -> pd.DataFrame:
    # Filter for managers (employees with null reports_to)
    managers = employees[employees['reports_to'].isna()][['employee_id', 'name']]
    
    # Self-join to find reporting relationships
    merged = managers.merge(employees, left_on='employee_id', right_on='reports_to', how='left')
    
    # Group by manager and count reports
    result = merged.groupby(['employee_id_x', 'name_x']).agg(
        reports_count=('employee_id_y', 'count'),
        average_age=('age_y', 'mean')
    ).reset_index()
    
    # Rename columns
    result.columns = ['employee_id', 'name', 'reports_count', 'average_age']
    
    # Round average_age to nearest integer, keep null for no reports
    result['average_age'] = result['average_age'].round().astype('Int64')
    
    # Sort by employee_id
    result = result.sort_values('employee_id')
    
    return result[['employee_id', 'name', 'reports_count', 'average_age']]

Complexity

• Time: O(n) where n is the number of employees
• Space: O(n) for storing the result
• Notes: The LEFT JOIN ensures we include managers with no reports, and AVG returns NULL for empty groups

Approach 2: Subquery with Aggregation

Intuition Use a subquery to count reports and calculate average age for each manager, then join with the main table to get manager information, filtering only for employees who don’t report to anyone.

Steps

• Create a subquery that groups by reports_to and counts employees
• Calculate the average age for each group in the subquery
• Join this subquery with the original Employees table on employee_id = reports_to
• Filter for employees where reports_to is null (managers only)
• Handle null values for managers with no reports
• Order the result by employee_id

import pandas as pd
import numpy as np

def count_employees(employees: pd.DataFrame) -> pd.DataFrame:
    # Group by reports_to to count and calculate average age
    reports = employees[employees['reports_to'].notna()].groupby('reports_to').agg(
        reports_count=('employee_id', 'size'),
        average_age=('age', 'mean')
    ).reset_index()
    reports.columns = ['employee_id', 'reports_count', 'average_age']
    
    # Round average_age
    reports['average_age'] = reports['average_age'].round().astype('Int64')
    
    # Filter for managers and merge with reports
    managers = employees[employees['reports_to'].isna()][['employee_id', 'name']]
    result = managers.merge(
        reports, 
        on='employee_id', 
        how='left'
    )
    
    # Fill NaN values for managers with no reports
    result['reports_count'] = result['reports_count'].fillna(0).astype(int)
    
    # Sort by employee_id
    result = result.sort_values('employee_id')
    
    return result

Complexity

• Time: O(n) where n is the number of employees
• Space: O(n) for storing intermediate results
• Notes: Using subqueries can sometimes be more readable for complex aggregations and filtering