Watch 10 video solutions for The Number of Employees Which Report to Each Employee, a easy level problem involving Database. This walkthrough by Learn With Chirag has 10,207 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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 | +-------------+---------+---------------+-------------+
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 | 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 |
