Managers with at Least 5 Direct Reports

LeetCode 570 | Difficulty: Medium

Medium

Problem Description

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| department  | varchar |
| managerId   | int     |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the name of an employee, their department, and the id of their manager.
If managerId is null, then the employee does not have a manager.
No employee will be the manager of themself.

Write a solution to find managers with at least five direct reports.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input: 
Employee table:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+
Output: 
+------+
| name |
+------+
| John |
+------+

Topics: Database

---

Solutions

Solution 1: MySQL (Best: 743 ms)

Metric	Value
Runtime	743 ms
Memory	N/A
Date	2018-05-08

Solution

# Write your MySQL query statement below

select Name from employee where Id in
(SELECT ManagerId from Employee group by ManagerId having count(*) > 4)

---

Complexity Analysis

Approach	Time	Space
Solution	$O(n)$	$O(1) to O(n)$

---

Interview Tips

Key Points

• Discuss the brute force approach first, then optimize. Explain your thought process.
• LeetCode provides 4 hint(s) for this problem — try solving without them first.

💡 Hints

Hint 1: Try to get all the mangerIDs that have count bigger than 5

Hint 2: Use the last hint's result as a table and do join with origin table at id equals to managerId

Hint 3: This is a very good example to show the performance of SQL code. Try to work out other solutions and you may be surprised by running time difference.

Hint 4: If your solution uses 'IN' function and runs more than 5 seconds, try to optimize it by using 'JOIN' instead.