[MySQL] 리트코드 570. Managers with at Least 5 Direct Reports

문제

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.

 

https://leetcode.com/problems/managers-with-at-least-5-direct-reports/description/

예시

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

문제 풀이

-- JOIN 활용
SELECT
    e2.name
FROM 
    employee AS e1
JOIN
    employee AS e2 ON e1.managerid = e2.id
GROUP BY
    e1.managerid
HAVING
    COUNT(e1.managerid) >= 5;

-- SUBQUERY 활용
SELECT
    e2.name
FROM 
    employee AS e2
WHERE
    e2.id IN (
        SELECT
            e1.managerid
        FROM
            employee AS e1
        GROUP BY
            e1.managerid
        HAVING
            COUNT(e1.managerid) >= 5
    );

---

SQL을 독학하시는 분들에게 도움이 되길 바라며,

혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.