[SQL/코드카타] 직속 하위 직원이 5명 이상인 매니저 찾기

문제

 

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

📌 문제 요약

직속 하위 직원이 적어도 5명 이상인 매니저를 찾으세요.

• id와 같은 다른 행의 managerid가 5행 이상인 것 찾기
• manager 찾아서 그 이름 출력

---

다양한 풀이방법

1. WHERE절 서브쿼리 (중첩 서브쿼리)

SELECT a.name
FROM employee a
WHERE id IN (SELECT managerid
            FROM employee b
            GROUP BY b.managerid
            HAVING COUNT(b.managerid) >= 5)

 

2. JOIN과 FROM 절 서브쿼리 (인라인 뷰)

SELECT a.name
FROM employee a
JOIN (
    SELECT managerid, COUNT(*) AS directreports
    FROM employee
    GROUP BY managerid
    HAVING COUNT(*) >=5
) b ON a.id = b.managerid

 

3. JOIN과 GROUP BY 활용

SELECT a.name
FROM employee a
JOIN employee b ON a.id=b.managerid
GROUP BY b.managerid
HAVING COUNT(b.managerid) >=5