Managers with at Least 5 Direct Reports solution and explanation
Deloitte
Google
TCS
9 more videos on this problem
Managers with at Least 5 Direct Reports | Leetcode 570 | Crack SQL Interviews in 50 Qs #mysqlLearn With Chirag · 7:22 · 21K views
13. Managers with at least 5 direct reports | SQL Interview Questions and AnswersStart Practicing · 5:23 · 15K views
LeetCode 570: Managers with at Least 5 Direct Reports [SQL]Frederik Müller · 6:46 · 10K views
LeetCode Medium 570 "Managers with 5 Direct Reports" Amazon Interview SQL Question With ExplanationEveryday Data Science · 5:31 · 8K views
Leetcode 570 - Managers with atleast 5 direct reports - Python Solution | FAANG InterviewsEveryday Data Science · 5:40 · 938 views
LeetCode 570 - Managers With at Least 5 Direct Reports (Python and SQL) [Medium]Ryan & Matt Data Science · 7:25 · 733 views
LeetCode - 570. Managers with at Least 5 Direct Reports (SQL)Chain Of Thought · 5:13 · 465 views
LeetCode MSSQL 570: Managers with at Least 5 Direct ReportsShantanu Khond · 4:22 · 238 views
Managers with at Least 5 Direct Reports | SQL | Leetcode | Easy Explaination | TOP 50 QUESTION | IMPSheCodes · 7:51 · 106 views
FAANG Task#13 - Manager with at least 5 Direct Reports | LEETCODE | TUITIONS TONIGHTTuitions Tonight · 5:19 · 233 views
The problem
Table: Employee
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | department | varchar | | managerId | int | +-------------+---------+ id is the primary key (column with unique values) for this table. Each row of this table indicates the name of an employee, their department, and the id of their manager. If managerId is null, then the employee does not have a manager. No employee will be the manager of themself.
Write a solution to find managers with at least five direct reports.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Employee table: +-----+-------+------------+-----------+ | id | name | department | managerId | +-----+-------+------------+-----------+ | 101 | John | A | null | | 102 | Dan | A | 101 | | 103 | James | A | 101 | | 104 | Amy | A | 101 | | 105 | Anne | A | 101 | | 106 | Ron | B | 101 | +-----+-------+------------+-----------+ Output: +------+ | name | +------+ | John | +------+
Approach overview
Problem Overview: The task is to return the names of managers who have at least five direct reports in the Employee table. Each row stores an employee and their manager using managerId. You need to identify managers whose employee count is five or more and return their names.
Approach 1: SQL Group By and Having Clause (Time: O(n), Space: O(1))
The most direct solution groups employees by managerId and counts how many employees report to each manager. Using GROUP BY managerId aggregates rows for the same manager, and the HAVING COUNT(*) >= 5 condition filters only those with at least five reports. A join back to the Employee table retrieves the manager's name. This approach works because SQL aggregation handles counting efficiently during a single scan of the table.
Approach 2: Combination of Join and Group By (Time: O(n), Space: O(1))
This approach explicitly joins the employee table with itself using a self-relation between employee and manager records. The join matches e.managerId = m.id, pairing each employee with their manager. After the join, group results by the manager’s ID or name and apply HAVING COUNT(e.id) >= 5. This structure is common when you want both manager attributes and aggregated employee counts in one query.
Approach 3: Grouping and Counting Managers (Time: O(n), Space: O(1))
Another variation first computes manager IDs that satisfy the count condition using a grouped subquery. The subquery returns manager IDs with COUNT(*) >= 5. The outer query then filters employees whose id appears in that list. This approach separates aggregation logic from the final selection step, which can improve readability when queries grow more complex.
Approach 4: Using Self Join (Time: O(n), Space: O(1))
A self join treats the Employee table as two logical tables: one representing managers and another representing employees. Each employee row is joined with their manager row through managerId. After joining, grouping by the manager and counting employee rows reveals how many direct reports each manager has. Self joins are a common technique in hierarchical datasets such as organizational charts or tree-like relationships.
These techniques rely heavily on SQL aggregation and relational joins. If you want to strengthen the fundamentals behind these queries, review concepts like SQL, GROUP BY aggregation, and table joins.
Recommended for interviews: The GROUP BY + HAVING approach is the expected solution. It directly expresses the requirement—count employees per manager and filter those counts. Interviewers prefer this because it demonstrates strong understanding of SQL aggregation and efficient query design. Variations using joins or subqueries show deeper familiarity with relational query patterns.
Approach 1: SQL Group By and Having Clause
This approach uses SQL's GROUP BY and HAVING clause to count the number of employees managed by each manager. We group the employees by their managerId and count the occurrences to determine the number of direct reports. We then filter the groups having at least five direct reports and join back with the Employee table to get the names of these managers.
SELECT e.name FROM Employee e WHERE e.id IN (SELECT managerId FROM Employee WHERE managerId IS NOT NULL GROUP BY managerId HAVING COUNT(id) >= 5);First, we select the managers who have at least five direct reports by counting the number of employees corresponding to each managerId in the subquery. We then use this list to filter the original table and retrieve the names of these managers from the Employee table.
The time complexity of this query depends on the indexing and size of the original table. In general, it's O(n) for counting and filtering operations, where n is the number of rows in the Employee table.
Approach 2: Combination of Join and Group By
This approach utilizes a join between the table itself, followed by a GROUP BY and filter with a HAVING clause. We join the Employee table with itself on the condition of matching the managerId to id. This join helps to create pairs of managers and their direct reports. By grouping based on the manager's id and applying a HAVING clause, we fetch managers with five or more reports.
SELECT e1.name FROM Employee e1 JOIN Employee e2 ON e1.id = e2.managerId GROUP BY e1.id HAVING COUNT(e2.id) >= 5;In this implementation, we first join the table Employee with itself to create pairs between managers and their direct reports. We then group by the manager's id, counting the number of reports for each manager to filter those with at least five.
The time complexity for this query is similar to the previous approach, influenced by both the number of rows to join and then group. Generally, it would be about O(n) constrained within the database engine optimizations.
Approach 3: Grouping and Counting Managers
This approach involves using a SQL query to group employees by their managerId and then counting how many employees report to each manager. From this, we can filter out managers who have less than five direct reports.
SELECT e.name
FROM Employee e
JOIN (SELECT managerId
FROM Employee
GROUP BY managerId
HAVING COUNT(*) >= 5) m
ON e.id = m.managerId;We first create a subquery to select managerId from the Employee table, grouping by managerId and including only those managerIds with five or more entries. Then, we join this result with the Employee table to get the names of these managers.
Time Complexity: O(n), where n is the number of employees as we scan the list and group it.
Space Complexity: O(m), where m is the number of unique managerId entries that meet the criteria.
Approach 4: Using Self Join
This solution uses a self join within the table and counts the direct reports for each manager by referring to the managerId.
SELECT e1.name
FROM Employee e1
JOIN Employee e2 ON e1.id = e2.managerId
GROUP BY e1.id, e1.name
HAVING COUNT(e2.id) >= 5;Here, we perform a self join on the Employee table. We join on e1.id and e2.managerId, essentially matching each manager with their subordinates. We then group by the manager's id to count how many subordinates each has, and filter to keep those with at least 5 subordinates.
Time Complexity: O(n), due to the need to scan and join the relationships within the table.
Space Complexity: O(m), where m is the number of distinct managers in the table.
Approach 5: Grouping and Joining
We can first count the number of direct subordinates for each manager, and then join the Employee table to find the managers whose number of direct subordinates is greater than or equal to 5.
import pandas as pd
def find_managers(employee: pd.DataFrame) -> pd.DataFrame:
# Group the employees by managerId and count the number of direct reports
manager_report_count = (
employee.groupby("managerId").size().reset_index(name="directReports")
)
# Filter managers with at least five direct reports
result = manager_report_count[manager_report_count["directReports"] >= 5]
# Merge with the Employee table to get the names of these managers
result = result.merge(
employee[["id", "name"]], left_on="managerId", right_on="id", how="inner"
)
# Select only the 'name' column and drop the 'id' and 'directReports' columns
result = result[["name"]]
return resultComplexity comparison
| Approach | Complexity |
|---|---|
| SQL Group By and Having Clause | The time complexity of this query depends on the indexing and size of the original table. In general, it's O(n) for counting and filtering operations, where n is the number of rows in the |
| Combination of Join and Group By | The time complexity for this query is similar to the previous approach, influenced by both the number of rows to join and then group. Generally, it would be about O(n) constrained within the database engine optimizations. |
| Grouping and Counting Managers | Time Complexity: O(n), where n is the number of employees as we scan the list and group it. |
| Using Self Join | Time Complexity: O(n), due to the need to scan and join the relationships within the table. |
| Grouping and Joining | – |
Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL GROUP BY + HAVING | O(n) | O(1) | Best general solution when counting employees per manager |
| Join + GROUP BY | O(n) | O(1) | When manager details are needed during aggregation |
| Subquery with GROUP BY | O(n) | O(1) | When separating aggregation logic improves readability |
| Self Join | O(n) | O(1) | Useful for hierarchical relationships like employee-manager structures |
Frequently asked questions
Is Managers with at Least 5 Direct Reports easy or hard?
The problem is classified as Medium because it requires understanding SQL aggregation and relational joins. The logic is simple once you recognize that the key step is counting employees per manager using GROUP BY and filtering with HAVING.
Managers with at Least 5 Direct Reports Python/Java solution
This problem is designed for SQL rather than Python or Java. The solution is implemented as a database query using GROUP BY and HAVING. Application languages like Python or Java typically just execute the SQL query against the database.
How to solve Managers with at Least 5 Direct Reports in O(n)?
Scan the Employee table and group rows by managerId using GROUP BY. Count the number of employees per manager with COUNT(*). Apply HAVING COUNT(*) >= 5 to keep only managers with at least five direct reports, then join with Employee to fetch the manager names.
What is the best approach for Managers with at Least 5 Direct Reports?
The most efficient and commonly expected solution uses SQL GROUP BY with a HAVING clause. Group rows by managerId, count employees for each manager, and filter with HAVING COUNT(*) >= 5. Then join the result back to the Employee table to return the manager's name.
Is Managers with at Least 5 Direct Reports asked at Google/Amazon/Meta?
Database aggregation and employee hierarchy queries appear frequently in SQL interview rounds at companies like Amazon, Meta, and Google. Problems involving GROUP BY, HAVING, and self joins are common patterns used to evaluate SQL proficiency.
What data structure is used in Managers with at Least 5 Direct Reports?
The solution relies on relational database operations rather than traditional in-memory data structures. SQL aggregation groups rows logically by managerId, while joins reconstruct relationships between employees and their managers.
What is the time complexity of Managers with at Least 5 Direct Reports?
The query typically runs in O(n) time where n is the number of rows in the Employee table. The database scans the table once to group rows by managerId and compute counts. Space complexity is O(1) outside of internal database aggregation structures.