I am an amazing person.

최대 1 분 소요

문제 링크

정답

WITH Managers as (
    SELECT 
        reports_to id, 
        COUNT(*) reports_count,
        ROUND(AVG(age),0) average_age
    FROM Employees
    GROUP BY reports_to
    HAVING reports_to IS NOT NULL
)

SELECT 
    E.employee_id,
    E.name,
    M.reports_count,
    M.average_age
FROM Employees E
JOIN Managers M
ON E.employee_id = M.id
ORDER BY E.employee_id;

✅ Point

  • Manager 조건을 충족시키는 row 로 CTE 선언
    • GROUP BY reports_to : 보고할 멤버(reports_to, 곧 Manager을 의미) 묶기
    • HAVING reports_to IS NOT NULL: GROUP BY의 column에 조건 부여. null인 경우, 매니저 x

다른 풀이

  • CTE를 선언하지 않고 JOIN 으로 풀기 
    SELECT
  M.employee_id, M.name,
  COUNT(*) reports_count,
  ROUND(AVG(E.age), 0) average_age
FROM Employees E
INNER JOIN Employees M
ON E.reports_to = M.employee_id
GROUP BY M.employee_id
ORDER BY M.employee_id;

참고