570. Managers with at Least 5 Direct Reports(Medium)

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 |
+------+

 

- 해설

managerId가 5번 이상 나온 직원의 이름을 출력하는 문제.

셀프 조인과 group by만 적절히 활용하면 되는 문제였다.

 

-- 풀이
select e2.name
  from employee e1
  join employee e2
    on e1.managerId = e2.id
  group by e2.id
  having count(e2.id) >= 5;

 

 

** 덧

이 문제 풀다 헷갈렸던 내용을 정리해 두었다. count는 where절에 나오는게 맞는가? 다른 곳에 나와야 하는가?

 

[STUDY/SQL] - MYSQL - Invalid use of group function

MYSQL - Invalid use of group function