Managers with at Least 5 Direct Reports - Problem

You are given a table Employee that contains information about employees, their departments, and their managers.

Each row represents an employee with their unique id, name, department, and the managerId of their direct manager. If managerId is NULL, the employee has no manager.

Task: Write a SQL query to find managers who have at least 5 direct reports.

Return the result in any order containing the names of these managers.

Table Schema

Employee
Column Name Type Description
id PK int Primary key - unique employee ID
name varchar Employee's full name
department varchar Department where employee works
managerId int ID of the employee's direct manager (NULL if no manager)
Primary Key: id
Note: No employee will be the manager of themselves. managerId references another employee's id.

Input & Output

Example 1 — Manager with Exactly 5 Reports
Input Table:
id name department managerId
101 John A
102 Dan A 101
103 Brad A 101
104 George A 101
105 Kyle A 101
106 Lisa A 101
Output:
name
John
💡 Note:

John (id=101) has exactly 5 direct reports: Dan, Brad, George, Kyle, and Lisa all have managerId=101. Since this meets our criteria of at least 5 direct reports, John is included in the result.
Example 2 — Multiple Managers with Different Report Counts
Input Table:
id name department managerId
101 Alice Engineering
102 Bob Engineering
103 Charlie Engineering 101
104 David Engineering 101
105 Eve Marketing 102
106 Frank Marketing 102
Output:
name
💡 Note:

Alice has 2 direct reports (Charlie, David), and Bob has 2 direct reports (Eve, Frank). Neither manager has at least 5 direct reports, so the result is empty.
Example 3 — Manager with More Than 5 Reports
Input Table:
id name department managerId
201 Sarah Sales
202 Tom Sales 201
203 Jerry Sales 201
204 Mary Sales 201
205 Peter Sales 201
206 Anna Sales 201
207 Mike Sales 201
208 Linda Sales 201
Output:
name
Sarah
💡 Note:

Sarah (id=201) has 7 direct reports: Tom, Jerry, Mary, Peter, Anna, Mike, and Linda. Since 7 ≥ 5, Sarah qualifies as a manager with at least 5 direct reports.

Constraints

  • 1 ≤ Employee.id ≤ 500
  • 1 ≤ Employee.name.length ≤ 20
  • 1 ≤ Employee.department.length ≤ 15
  • All employee id values are unique
  • managerId is NULL or references a valid employee id

Visualization

Tap to expand
Managers with At Least 5 Direct Reports INPUT: Employee Table id name dept mgrId 101 John A NULL 102 Dan A 101 103 James A 101 104 Amy A 101 105 Anne A 101 106 Ron A 101 Hierarchy View: John (101) Dan James Amy Anne Ron 5 direct reports ALGORITHM STEPS 1 Self-Join Table Join Employee e1 with e2 ON e1.id = e2.managerId 2 Group By Manager GROUP BY e1.id, e1.name 3 Count Reports COUNT(e2.id) as reports 4 Filter by HAVING HAVING COUNT(*) >= 5 SQL Query: SELECT e1.name FROM Employee e1 JOIN Employee e2 ON e1.id = e2.managerId GROUP BY e1.id, e1.name HAVING COUNT(*) >= 5; FINAL RESULT Count per Manager: John: 5 reports OK Others: <5 Output Table: name John Result: 1 row Manager found: John John has exactly 5 direct reports (Dan, James, Amy, Anne, Ron) Key Insight: Self-join allows comparing each employee with potential reports. GROUP BY aggregates by manager, and HAVING filters groups after aggregation. This is more efficient than subqueries with COUNT. Time Complexity: O(n) where n = number of employees. Space Complexity: O(m) for m managers. TutorialsPoint - Managers with at Least 5 Direct Reports | Optimal Solution (Self-Join with GROUP BY)

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 19 Apple 15
78.5K Views
High Frequency
~12 min Avg. Time
1.9K 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