Managers with at Least 5 Direct Reports - Video Solutions

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.