[HackerRank] New Companies

 

 

[문제]

이미지를 누르면 문제 링크로 넘어갑니다

 

 

[MySQL]

SELECT c.*
     , COUNT(DISTINCT l.lead_manager_code) AS LM
     , COUNT(DISTINCT s.senior_manager_code) AS CM
     , COUNT(DISTINCT m.manager_code) AS M
     , COUNT(DISTINCT e.employee_code) AS E
FROM Company AS c
    LEFT JOIN Lead_Manager AS l ON c.company_code = l.company_code
    LEFT JOIN Senior_Manager AS s ON l.lead_manager_code = s.lead_manager_code
    LEFT JOIN Manager AS m ON s.senior_manager_code = m.senior_manager_code
    LEFT JOIN Employee AS e ON m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code