Managers with at Least 5 Direct Reports | LeetCode 570

1. Chapter Overview and Problem Statement

This chapter covers problem number 570 from LeetCode, titled "Managers with at Least 5 Direct Reports", part of the 50-question SQL interview series. The objective is to write a SQL query that identifies and returns the names of managers who have at least five employees directly reporting to them.

Input Schema (Employee Table)

The database contains a single table named Employee with the following columns:

Key Conditions & Business Rules

• Primary Key: The id column uniquely identifies each record.
• Reporting Hierarchy: Each row represents an employee, their department, and their manager's ID.
• Null Managers: If manager_id is NULL, the employee does not have a manager (e.g., top-level executives).
• No Self-Management: No employee will be the manager of themselves (id != manager_id).
• Target Output: Return the name of any manager who has $\ge 5$ direct reports. Results can be returned in any order.

2. Understanding the Data Through an Example

Let us examine a sample Employee table to understand how direct reports are mapped via manager_id:

Analyzing the Report Counts

• Looking at the manager_id column, the ID 101 appears 5 times (for Dan, James, Amy, Anne, and Ron).
• Checking whose id corresponds to 101, we find that John (id = 101) is their manager.
• Because John has exactly 5 direct reports, his name (John) satisfies the condition ($\ge 5$) and must be included in the final result.

3. The Core Mental Model & Strategy: Self-Join

Because the problem requires us to retrieve the manager's name based on employee records stored inside the same table, we must correlate the table with itself. This is known as a Self-Join.

Real-Life Analogy

Imagine a company directory where everyone's name and badge number are listed on a master sheet, along with their boss's badge number. To find out who manages whom, you take two copies of the exact same master sheet side-by-side:

• Sheet 1 (e1): Used to look up the manager's actual name using their badge ID (e1.id).
• Sheet 2 (e2): Used to look up all the employees who work under that badge ID (e2.manager_id).

By aligning Sheet 1's employee ID with Sheet 2's manager ID, you instantly pair every manager with all of their subordinates.

4. Naive Approach: Using Inner Join Without Aggregation

If we perform an inner join of the Employee table with itself where the manager's ID matches the employee's ID, we pair each manager with their direct reports.

Naive SQL Query

SELECT *
FROM Employee e1
INNER JOIN Employee e2
  ON e1.id = e2.manager_id;

What Happens Here?

• By default, omitting the join type keyword defaults to an INNER JOIN, which returns only matching rows.
• The condition e1.id = e2.manager_id matches the manager's primary key (id) with the subordinate's manager_id.
• Running this query without grouping generates multiple rows for managers with multiple reports (e.g., John's record will appear 5 times because 5 employees report to him).

5. Step-by-Step Evolution to the Final Solution

To convert our raw joined rows into a clean list of managers with 5 or more reports, we need two SQL constructs:

1. GROUP BY: Collapses multiple rows belonging to the same manager into a single aggregated row.
2. HAVING: Filters the aggregated groups based on an aggregate condition (since WHERE cannot filter aggregate function results like COUNT()).

Final Optimized SQL Query

SELECT e1.name
FROM Employee e1
INNER JOIN Employee e2
  ON e1.id = e2.manager_id
GROUP BY e2.manager_id
HAVING COUNT(e2.manager_id) >= 5;

6. Complete Flow Walkthrough of the Execution

Let's trace how the final query executes step by step against our database:

7. Alternative Approaches & Industry Best Practices

While a Self-Join with GROUP BY and HAVING is standard and highly intuitive, this problem can also be solved using subqueries and window functions.

Alternative 1: Using a Subquery with IN and GROUP BY

You can first isolate the manager IDs that satisfy the condition using a subquery, then fetch their names from the main table:

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

• Why use this? It avoids joining the table to itself, which can be beneficial for readability and performance depending on database indexing.

8. Summary and Key Takeaways

• Self-Joins: Essential when querying hierarchical relationships stored within a single table by aliasing the table twice (e.g., Employee e1 and Employee e2).
• Join Condition: Match the manager's primary key with the employee's foreign key (e1.id = e2.manager_id).
• Aggregation (GROUP BY): Group records by the manager's ID to aggregate all direct reports under a single entity.
• Filtering Aggregates (HAVING): Always use HAVING instead of WHERE when applying conditional checks on aggregate functions like COUNT().
• Primary Key Integrity: The id column ensures unique identification, preventing ambiguity during row matching.
• Handling Nulls: Employees with NULL as their manager_id represent top-level staff and are automatically excluded from inner join matches where manager_id must reference a valid id.
• Alternative Patterns: Subqueries with IN clauses offer a clean, non-joined alternative to achieve the exact same result.