Managers with at Least 5 Direct Reports

medium database sql

Problem

From Employee(id, name, managerId), return names of managers with ≥ 5 direct reports.

Inputrows = [(101,'A',Null),(102,'B',101),(103,'C',101),(104,'D',101),(105,'E',101),(106,'F',101),(107,'G',101)]

Output['A']

One pass over rows tallying managerId; filter ≥ 5; lookup name by id.

rows (id,name,managerId ; …, "null" for no manager)

SELECT name FROM Employee
WHERE id IN (SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) >= 5);

Explanation

The question asks for the names of managers, but the only place that tells us how many people report to someone is the managerId column. So the trick is to count first, then look up the names.

The inner query does the counting: GROUP BY managerId buckets every employee row by who their manager is, and HAVING COUNT(*) >= 5 keeps only the buckets that have at least five reports. The result is a list of manager ids.

The outer query then turns those ids back into names. WHERE id IN (...) selects the Employee rows whose id appears in that list of busy managers, and we read off their name.

Example: with rows where 102 through 107 all have managerId = 101, the grouping gives 101 → 6. Six is at least five, so id 101 survives, and the outer query looks up id 101 to return name 'A'.

Note we group on managerId (the person being reported to) but match on id (that same person's own row), which is how one table answers a question about itself.

Time: O(n) Space: O(n)