[Hacker Rank] SQL Intermediate 'New Companies' (Medium)

New Companies | HackerRank

New Companies | HackerRank

SELECT
    C.company_code,
    C.founder,
    COUNT(DISTINCT LM.lead_manager_code) AS total_lead_managers,
    COUNT(DISTINCT SM.senior_manager_code) AS total_senior_managers,
    COUNT(DISTINCT M.manager_code) AS total_managers,
    COUNT(DISTINCT E.employee_code) AS total_employees
FROM
    Company AS C
LEFT JOIN
    Lead_Manager AS LM ON C.company_code = LM.company_code
LEFT JOIN
    Senior_Manager AS SM ON C.company_code = SM.company_code
LEFT JOIN
    Manager AS M ON C.company_code = M.company_code
LEFT JOIN
    Employee AS E ON C.company_code = E.company_code
GROUP BY
    C.company_code,
    C.founder
ORDER BY
    C.company_code ASC;

 

POINT!

계층 구조는 회사-리드 매니저-시니어 매니저-매니저- 직원 으로 구성되어있습니다.

테이블 간의 JOIN을 할 때, 각 지위 별로 company_code를 가지고 있으므로 이 필드를 기준으로 모든 테이블을 JOIN해야합니다.