The Number of Employees Which Report to Each Employee - Solution & Explanation
Problem Statement
Table: Employees
+-------------+----------+ | 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).
For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.
Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.
Return the result table ordered by employee_id.
The result format is in the following example.
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 | +-------------+-------+---------------+-------------+ | 9 | Hercy | 2 | 39 | +-------------+-------+---------------+-------------+ Explanation: Hercy has 2 people report directly to him, Alice and Bob. Their average age is (41+36)/2 = 38.5, which is 39 after rounding it to the nearest integer.
Example 2:
Input: Employees table: +-------------+---------+------------+-----+ | employee_id | name | reports_to | age | |-------------|---------|------------|-----| | 1 | Michael | null | 45 | | 2 | Alice | 1 | 38 | | 3 | Bob | 1 | 42 | | 4 | Charlie | 2 | 34 | | 5 | David | 2 | 40 | | 6 | Eve | 3 | 37 | | 7 | Frank | null | 50 | | 8 | Grace | null | 48 | +-------------+---------+------------+-----+ Output: +-------------+---------+---------------+-------------+ | employee_id | name | reports_count | average_age | | ----------- | ------- | ------------- | ----------- | | 1 | Michael | 2 | 40 | | 2 | Alice | 2 | 37 | | 3 | Bob | 1 | 37 | +-------------+---------+---------------+-------------+
Approach Overview
Problem Overview: You are given an Employees table where each row contains an employee id, name, age, and the id of the manager they report to. The goal is to compute, for every manager, the number of direct reports and the average age of those employees. Only employees who actually manage others should appear in the result.
Approach 1: Sorting and Two-Pointer Technique (O(n log n) time, O(1) extra space)
Start by sorting the employee records based on the reports_to (manager id). After sorting, employees reporting to the same manager appear in contiguous blocks. Use a two-pointer scan to process each block: the left pointer marks the start of employees reporting to a specific manager, and the right pointer expands while the manager id stays the same. During this scan, count employees and accumulate their ages to compute the average. This approach mirrors how a database performs grouped aggregation after sorting. Sorting dominates the complexity at O(n log n), while the two-pointer traversal is O(n). This technique is useful when the data is already sorted or when you want minimal auxiliary structures. See related concepts in sorting and two pointers.
Approach 2: Hashing for Quick Lookup (O(n) time, O(n) space)
Use a hash map where the key is the manager id (reports_to) and the value stores aggregated information: employee count and total age. Iterate through the employee list once. For each employee that reports to a manager, update the manager's entry in the hash map by incrementing the count and adding the employee's age. After the scan, compute the average age for each manager using total_age / count. This avoids sorting and processes the data in a single pass, giving O(n) time complexity with O(n) additional space. The approach mirrors a GROUP BY aggregation in SQL but implemented with an in-memory structure. Learn more about this pattern in hashing and aggregation problems.
Recommended for interviews: The hashing approach is the expected solution because it achieves linear time with a single pass and clearly demonstrates understanding of aggregation using hash maps. The sorting + two-pointer approach still shows solid problem-solving skills and can be useful when memory is constrained or when the input is already ordered.
Approach 1: Approach 1: Sorting and Two-Pointer Technique
This approach involves sorting the data first and then using a two-pointer technique. By sorting, we can simplify the problem as the elements will be in order. The two-pointer method then efficiently checks possible solutions by limiting the number of checks needed.
In this solution, we begin by sorting the array using the qsort function from the standard library. Next, we use two pointers initialized at the beginning and end of the array. We iterate, moving the pointers based on their summed value compared to the target, hence optimizing the search.
Complexity
Time complexity is O(n log n) due to sorting, and space complexity is O(1).
Approach 2: Approach 2: Hashing for Quick Lookup
This approach uses a hash table (or dictionary) to keep track of the elements and their complements needed to reach the target sum. By utilizing this lookup, we can reduce the problem to a linear time complexity.
This C solution uses a simple hash table implementation with linear probing for collision resolution, storing elements as they are checked. If their complement (sum - element) is found in the hash table, the pair exists.
Complexity
Time complexity is O(n) assuming uniform distribution of hash function (no collisions), and space complexity is O(n).
Approach 3: Self-Join + Grouping
We can use self-join to connect the information of each employee's superior manager to the information of each employee, and then use grouping and aggregation to count the number of subordinates and the average age of each manager.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| Approach 1: Sorting and Two-Pointer Technique | Time complexity is |
| Approach 2: Hashing for Quick Lookup | Time complexity is |
| Self-Join + Grouping | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Sorting and Two-Pointer Technique | O(n log n) | O(1) | When data can be sorted or is already ordered by manager id |
| Hashing for Quick Lookup | O(n) | O(n) | General case; fastest approach for aggregating counts and averages |
Video Solution
The Number of Employees Which Report to Each Employee | Leetcode 1731 | Crack SQL Interviews • Learn With Chirag • 11,348 views views
Watch 9 more video solutions →Frequently Asked Questions
Is The Number of Employees Which Report to Each Employee easy or hard?
The Number of Employees Which Report to Each Employee Python/Java solution
How to solve The Number of Employees Which Report to Each Employee in O(n)?
What is the best approach for The Number of Employees Which Report to Each Employee?
Is The Number of Employees Which Report to Each Employee asked at Google/Amazon/Meta?
What data structure is used in The Number of Employees Which Report to Each Employee?
What is the time complexity of The Number of Employees Which Report to Each Employee?
Ready to solve this problem?
Practice The Number of Employees Which Report to Each Employee with our built-in code editor and test cases.
Practice on FleetCode