The Number of Employees Which Report to Each Employee

The Number of Employees Which Report to Each Employee - Problem

Database Easy

The Number of Employees Which Report to Each Employee

You're working with a company's employee database and need to generate a management report that shows key statistics about managers and their direct reports.

Given a table Employees with the following structure:

Column Name	Type
employee_id	int
name	varchar
reports_to	int
age	int

Key Information:

employee_id is unique for each employee
reports_to contains the manager's employee_id (null if no manager)
A manager is defined as someone with at least 1 direct report

Your Goal: Create a report showing for each manager:

Their employee_id and name
The count of employees reporting directly to them
The average age of their direct reports (rounded to nearest integer)

Return results ordered by employee_id.

Input & Output

example_1.sql — Basic Company Structure

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

› Output: | employee_id | name | reports_count | average_age |\n|-------------|-------|---------------|-------------|\n| 9 | Hercy | 2 | 39 |

💡 Note: Hercy (ID: 9) is the only manager with direct reports. Alice (41) and Bob (36) both report to Hercy. Count = 2, Average age = (41 + 36) / 2 = 38.5, rounded to 39.

example_2.sql — Multiple Managers

$ Input: Employees table:\n| employee_id | name | reports_to | age |\n|-------------|---------|------------|-----|\n| 1 | Boss | null | 60 |\n| 2 | Alice | 1 | 30 |\n| 3 | Bob | 1 | 40 |\n| 4 | Charlie | 2 | 25 |\n| 5 | David | 2 | 35 |

› Output: | employee_id | name | reports_count | average_age |\n|-------------|-------|---------------|-------------|\n| 1 | Boss | 2 | 35 |\n| 2 | Alice | 2 | 30 |

💡 Note: Boss manages Alice and Bob (ages 30, 40) → average 35. Alice manages Charlie and David (ages 25, 35) → average 30. Results ordered by employee_id.

example_3.sql — Edge Case with Rounding

$ Input: Employees table:\n| employee_id | name | reports_to | age |\n|-------------|------|------------|-----|\n| 1 | John | null | 50 |\n| 2 | Jane | 1 | 33 |\n| 3 | Jack | 1 | 34 |\n| 4 | Jill | 1 | 35 |

› Output: | employee_id | name | reports_count | average_age |\n|-------------|------|---------------|-------------|\n| 1 | John | 3 | 34 |

💡 Note: John manages 3 employees with ages 33, 34, 35. Average = (33 + 34 + 35) / 3 = 34.0, which rounds to 34.

Constraints

1 ≤ employees.length ≤ 10⁴
1 ≤ employee_id ≤ 10⁶
1 ≤ age ≤ 100
reports_to can be null (for top-level employees)
All employee_id values are unique
Employee names are non-empty strings

Visualization

Tap to expand

Understanding the Visualization

Identify Relationships

Map each employee to their direct manager using reports_to field

Group by Manager

Automatically group all employees under their respective managers

Calculate Statistics

Count direct reports and compute average age for each manager's team

Generate Report

Format results with manager details and sort by employee ID

Key Takeaway

🎯 Key Insight: GROUP BY with aggregate functions transforms hierarchical relationship data into management metrics efficiently, leveraging the database engine's optimization for O(n) performance.

Asked in

a Amazon 25 ⊞ Microsoft 18 G Google 15 f Meta 12

The optimal solution uses GROUP BY with aggregate functions to efficiently calculate manager statistics in a single table scan. Key insight: Self-join the Employees table on reports_to = employee_id, then group by manager and use COUNT() and ROUND(AVG()) to compute the required metrics. Time complexity: O(n) with excellent database optimization.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY Aggregation (Optimal)	O(n)	O(m)	Use GROUP BY with aggregate functions to calculate statistics in one pass
Brute Force (Nested Subqueries)	O(n²)	O(1)	Use correlated subqueries to count and calculate averages for each manager

GROUP BY Aggregation (Optimal) — Algorithm Steps

Join employees table with itself on reports_to = employee_id
Group the results by manager (reports_to)
Use COUNT(*) to count direct reports
Use AVG() with ROUND() to calculate average age
Order results by manager employee_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Single Table Scan

Read through employees table once

Group by Manager

Automatically group employees by their reports_to value

Calculate Aggregates

Compute COUNT and AVG for each group simultaneously

Join Manager Info

Add manager names and order results

Code -

solution.c — C

#include <stdio.h>

int main() {
    printf("Primary solution:\n");
    printf("%s\n",
        "SELECT \n"
        "    m.employee_id,\n"
        "    m.name,\n"
        "    COUNT(e.employee_id) as reports_count,\n"
        "    ROUND(AVG(e.age)) as average_age\n"
        "FROM Employees e\n"
        "INNER JOIN Employees m ON e.reports_to = m.employee_id\n"
        "GROUP BY m.employee_id, m.name\n"
        "ORDER BY m.employee_id;"
    );
    
    printf("\n\nAlternative solution:\n");
    printf("%s\n",
        "SELECT \n"
        "    reports_to as employee_id,\n"
        "    (SELECT name FROM Employees WHERE employee_id = reports_to) as name,\n"
        "    COUNT(*) as reports_count,\n"
        "    ROUND(AVG(age)) as average_age\n"
        "FROM Employees\n"
        "WHERE reports_to IS NOT NULL\n"
        "GROUP BY reports_to\n"
        "ORDER BY reports_to;"
    );
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table with efficient grouping and aggregation

✓ Linear Growth

Space Complexity

O(m)

Where m is the number of managers - space for grouping intermediate results

✓ Linear Space

24.5K Views

Medium Frequency

~15 min Avg. Time

890 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

GROUP BY Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler