LeetCode - 1731. The Number of Employees Which Report to Each Employee (MySQL)

Easy, SQL - SELF JOIN

RunTime : 531 ms

문제

For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.

Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.

Return the result table ordered by employee_id.

The result format is in the following example.

풀이

• 하나의 테이블을 두번 접근해 employee_id와 reports_to가 같은 것들을 찾아 조립하면 되는 문제
• 처음에 굉장히 비효율적으로 풀었다.

1. 처음 풀이 (2181 ms)

SELECT A.employee_id, A.name,
(SELECT COUNT(employee_id) FROM Employees WHERE reports_to = A.employee_id) as reports_count,
(SELECT ROUND(AVG(age), 0) FROM Employees WHERE reports_to = A.employee_id) as average_age
FROM Employees as A
HAVING reports_count > 0 AND average_age IS NOT NULL
ORDER BY employee_id

2. 효율적 풀이 갱신 (531 ms)

SELECT A.employee_id, A.name,
COUNT(B.employee_id) as reports_count,
ROUND(AVG(B.age), 0) as average_age
FROM Employees as A JOIN Employees as B ON A.employee_id = B.reports_to
GROUP BY 1, 2
ORDER BY 1

아무리봐도 LeetCode SQL Easy 어려운거 같은데...