[해커랭크]New Companies

문제

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

풀이

SELECT
    c.company_code,
    c.founder,
    COUNT(DISTINCT lead.lead_manager_code),
    COUNT(DISTINCT senior.senior_manager_code),
    COUNT(DISTINCT manager.manager_code),
    COUNT(DISTINCT employee.employee_code)
    
FROM
    Company c
    LEFT JOIN Lead_Manager lead
        ON c.company_code = lead.company_code
    LEFT JOIN Senior_Manager senior
        ON senior.lead_manager_code = lead.lead_manager_code
    LEFT JOIN Manager manager
        ON manager.senior_manager_code = senior.senior_manager_code
    LEFT JOIN Employee employee
        ON employee.manager_code = manager.manager_code

GROUP BY c.company_code, c.founder
ORDER BY c.company_code