The Number of Employees Which Report to Each Employee solution and explanation
Google
9 more videos on this problem
The Number of Employees Which Report to Each Employee | Leetcode 1731 | Crack SQL InterviewsLearn With Chirag · 11:11 · 11K views
LeetCode 1731 "Employees Which Report to Each Employee" Interview SQL Question with ExplanationEveryday Data Science · 7:01 · 5K views
LeetCode 1731: The Number of Employees Which Report to Each Employee [SQL]Frederik Müller · 12:51 · 1K views
Cracking LeetCode 1731 - The Number of Employees Which Report to Each Employee | SQL TutorialvanAmsen · 7:03 · 420 views
Leetcode SQL 50 | 1731. The Number of Employees Which Report to Each Employee SolutionTrang Ng · 8:57 · 327 views
#SQL Leetcode SQL 50 #30. 1731. The Number of Employees Which Report to Each Employee.The Code Samples · 7:09 · 203 views
Leetcode SQL 1731 - The Number of Employees Which Report to Each Employee🔥| 50 Days ChallengeDATA KE DAKU · 8:33 · 155 views
1731. The Number of Employees Which Report to Each Employee | Sql | #sql #leetcodeSheCodes · 11:40 · 110 views
The Number of Employees Which Report to Each Employee | Leetcode 1731Code with Siddhant · 4:45 · 87 views
30. The Number of Employees Which Reports to Each Employee | SQL Interview Questions and AnswersStart Practicing · 8:39 · 7K views
The problem
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.
def find_pair(arr, target_sum):
arr.sort()
left, right = 0, len(arr) - 1
while left < right:
current_sum = arr[left] + arr[right]
if current_sum == target_sum:
return f"Pair found: ({arr[left]}, {arr[right]})"
elif current_sum < target_sum:
left += 1
else:
right -= 1
return "No pair found"import java.util.Arrays;
public class PairFinder {
public static void findPair(int[] arr, int sum) {
Arrays.sort(arr);
int left = 0, right = arr.length - 1;
while (left < right) {
int current_sum = arr[left] + arr[right];
if (current_sum == sum) {
System.out.println("Pair found: (" + arr[left] + ", " + arr[right] + ")");
return;
}
if (current_sum < sum)
left++;
else
right--;
}
System.out.println("No pair found");
}
}#include <iostream>
#include <algorithm>
#include <vector>
void findPair(std::vector<int> &arr, int sum) {
std::sort(arr.begin(), arr.end());
int left = 0, right = arr.size() - 1;
while (left < right) {
int current_sum = arr[left] + arr[right];
if (current_sum == sum) {
std::cout << "Pair found: (" << arr[left] << ", " << arr[right] << ")\n";
return;
}
if (current_sum < sum)
left++;
else
right--;
}
std::cout << "No pair found\n";
}function findPair(arr, sum) {
arr.sort((a, b) => a - b);
let left = 0, right = arr.length - 1;
while (left < right) {
let current_sum = arr[left] + arr[right];
if (current_sum === sum) {
console.log(`Pair found: (${arr[left]}, ${arr[right]})`);
return;
} else if (current_sum < sum) {
left++;
} else {
right--;
}
}
console.log("No pair found");
}#include <stdio.h>
#include <stdlib.h>
int cmpfunc(const void *a, const void *b) {
return (*(int*)a - *(int*)b);
}
void findPair(int arr[], int n, int sum) {
qsort(arr, n, sizeof(int), cmpfunc);
int left = 0, right = n - 1;
while (left < right) {
int current_sum = arr[left] + arr[right];
if (current_sum == sum) {
printf("Pair found: (%d, %d)\n", arr[left], arr[right]);
return;
}
if (current_sum < sum)
left++;
else
right--;
}
printf("No pair found\n");
}using System;
public class PairFinder {
public static void FindPair(int[] arr, int sum) {
Array.Sort(arr);
int left = 0, right = arr.Length - 1;
while (left < right) {
int current_sum = arr[left] + arr[right];
if (current_sum == sum) {
Console.WriteLine($"Pair found: ({arr[left]}, {arr[right]})");
return;
}
if (current_sum < sum)
left++;
else
right--;
}
Console.WriteLine("No pair found");
}
}Python's built-in sort() method sorts the array. With two pointers, we can efficiently search towards the middle, combining elements to find the correct sum.
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.
def find_pair(arr, target_sum):
complements = set()
for value in arr:
complement = target_sum - value
if complement in complements:
return f"Pair found: ({value}, {complement})"
complements.add(value)
return "No pair found"import java.util.HashSet;
public class PairFinder {
public static void findPair(int[] arr, int sum) {
HashSet<Integer> seen = new HashSet<>();
for (int number : arr) {
int complement = sum - number;
if (seen.contains(complement)) {
System.out.println("Pair found: (" + number + ", " + complement + ")");
return;
}
seen.add(number);
}
System.out.println("No pair found");
}
}#include <iostream>
#include <unordered_set>
void findPair(int arr[], int n, int sum) {
std::unordered_set<int> complements;
for (int i = 0; i < n; ++i) {
int complement = sum - arr[i];
if (complements.find(complement) != complements.end()) {
std::cout << "Pair found: (" << arr[i] << ", " << complement << ")\n";
return;
}
complements.insert(arr[i]);
}
std::cout << "No pair found\n";
}function findPair(arr, sum) {
let complements = new Set();
for (let value of arr) {
let complement = sum - value;
if (complements.has(complement)) {
console.log(`Pair found: (${value}, ${complement})`);
return;
}
complements.add(value);
}
console.log('No pair found');
}#include <stdio.h>
#include <stdlib.h>
struct HashTableItem {
int key;
int value;
};
int hashFunction(int key, int size) {
return abs(key) % size;
}
void insert(int *hashTable, int size, int key) {
int index = hashFunction(key, size);
while (hashTable[index] != INT_MIN) {
index = (index + 1) % size;
}
hashTable[index] = key;
}
int search(int* hashTable, int size, int key) {
int index = hashFunction(key, size);
while (hashTable[index] != INT_MIN) {
if (hashTable[index] == key)
return 1;
index = (index + 1) % size;
}
return 0;
}
void findPair(int arr[], int n, int sum) {
int hashTable[1000];
for (int i = 0; i < 1000; i++) hashTable[i] = INT_MIN;
for (int i = 0; i < n; i++) {
int complement = sum - arr[i];
if (search(hashTable, 1000, complement)) {
printf("Pair found: (%d, %d)\n", arr[i], complement);
return;
}
insert(hashTable, 1000, arr[i]);
}
printf("No pair found\n");
}using System;
using System.Collections.Generic;
public class PairFinder {
public static void FindPair(int[] arr, int sum) {
HashSet<int> complements = new HashSet<int>();
foreach (var number in arr) {
int complement = sum - number;
if (complements.Contains(complement)) {
Console.WriteLine($"Pair found: ({number}, {complement})");
return;
}
complements.Add(number);
}
Console.WriteLine("No pair found");
}
}The Python solution uses a set to register elements already encountered. This allows quick checking of whether the complement is present upon each iteration, achieving the desired pair resolution efficiently.
Time complexity is O(n), 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.
# Write your MySQL query statement below
SELECT
e2.employee_id,
e2.name,
COUNT(1) AS reports_count,
ROUND(AVG(e1.age)) AS average_age
FROM
Employees AS e1
JOIN Employees AS e2 ON e1.reports_to = e2.employee_id
GROUP BY 1
ORDER BY 1;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 | – |
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 |
Frequently asked questions
Is The Number of Employees Which Report to Each Employee easy or hard?
The problem is classified as Easy. The main challenge is recognizing that it is a grouping and aggregation task. Once you apply a hash map or SQL-style GROUP BY thinking, the implementation becomes straightforward.
The Number of Employees Which Report to Each Employee Python/Java solution
In Python or Java, the solution typically uses a dictionary or HashMap to aggregate report counts and age sums per manager. Iterate through the employee records, update the map for each reports_to value, and compute the average age afterward. The overall complexity remains O(n) time and O(n) space.
How to solve The Number of Employees Which Report to Each Employee in O(n)?
Use a hash map keyed by the manager id from the reports_to column. For every employee, increment the manager's report count and add the employee's age to a running total. After iterating through all employees, compute the average age for each manager as total_age divided by count. This single-pass aggregation achieves O(n) time complexity.
What is the best approach for The Number of Employees Which Report to Each Employee?
The most efficient approach uses a hash map to aggregate information for each manager. During a single pass through the employee records, store the count of reports and the total age for every manager. After processing all rows, compute the average age for each manager. This approach runs in O(n) time with O(n) space.
Is The Number of Employees Which Report to Each Employee asked at Google/Amazon/Meta?
Problems involving aggregation, grouping, and relational data processing frequently appear in database and data engineering interviews at companies like Amazon, Google, and Meta. Variations often require SQL GROUP BY queries or implementing similar aggregation logic with hash maps.
What data structure is used in The Number of Employees Which Report to Each Employee?
A hash map is the primary data structure for the optimal solution. The map stores the manager id as the key and tracks two values: the number of employees reporting to that manager and the total age of those employees. This structure enables constant-time updates during iteration.
What is the time complexity of The Number of Employees Which Report to Each Employee?
The optimal solution runs in O(n) time where n is the number of employees. Each record is processed once while updating a hash map keyed by manager id. A sorting-based approach is also possible but requires O(n log n) time due to the sorting step.