Managers with at Least 5 Direct Reports

Managers with at Least 5 Direct Reports

What is this problem about?

This is a standard SQL database problem. You are given an Employee table that contains employee details, including their id, name, and their managerId (which points to the id of another employee in the same table). Your task is to write a query that returns the names of all managers who have at least 5 direct reports.

Why is this asked in interviews?

This question is a favorite for testing foundational SQL knowledge. It evaluates a candidate's ability to perform a Self-Join or use Aggregation with the HAVING clause on a hierarchical table. Interviewers use it to ensure you understand how foreign keys work when they reference the primary key of the exact same table, which is how org charts and directory structures are universally modeled.

Algorithmic pattern used

There are two main SQL patterns to solve this:

1. GROUP BY and HAVING: Group the table by managerId, count the occurrences, filter for counts $\ge 5$, and then join the result back to the Employee table to get the manager's name.
2. Self-Join: Join the Employee table to itself (e.g., Employee e1 JOIN Employee e2 ON e1.id = e2.managerId), group by the manager's ID, and apply the HAVING COUNT(e2.id) >= 5 filter.

Example explanation

Employee Table:

id	name	managerId
101	John	(null)
102	Dan	101
103	James	101
104	Amy	101
105	Anne	101
106	Ron	101

If we run SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) >= 5, we get:

• Group 101: count is 5. We then take 101 and find the name associated with id = 101. That is "John". John is the only manager with at least 5 direct reports.

Common mistakes candidates make

A frequent mistake is using a simple WHERE clause to count. You cannot use aggregate functions like COUNT() in a WHERE clause; you must use HAVING after a GROUP BY. Another common error is returning the managerId instead of the manager's name, failing to do the final join or subquery required to fetch the actual string requested by the prompt.

Interview preparation tip

When tackling the Managers with at Least 5 Direct Reports SQL problem, the subquery approach is often the fastest to write and least prone to join-explosion errors: SELECT name FROM Employee WHERE id IN (SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) >= 5). This cleanly separates the aggregation logic from the final data retrieval.