[해커랭크(HackerRank)] New Companies (MySQL)

문제

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy: 

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

Note:

• The tables may contain duplicate records.
• The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

코드

SELECT c.company_code
     , c.founder
     , count(distinct lm.lead_manager_code)
     , count(distinct sm.senior_manager_code)
     , count(distinct m.manager_code)
     , count(distinct e.employee_code)
FROM company inner join lead_manager lm on c.company_code = lm.company_code
             inner join senior_manager sm on c.company_code = s.company_code and
                                          l.lead_manager_code = sm.lead_manager_code
             inner join manager m on c.company_code = manager.company_code and
                                   lm.lead_manager_code = m.lead_manager_code and
                                   sm.senior_manager_code = m.senior_manager_code
             inner join employee e on c.company_code = e.company_code and
                                    lm.lead_manager_code = e.lead_manager_code and
                                    sm.senior_manager_code = e.senior_manager_code and
                                    m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder

• company, lead_manager, senior_manager, manager, employee 테이블을 모두 조인해서 founder와 그에 따른 직원들의 수를 count하는 문제
• join을 해야하는 문제라서 하긴 했는데 이렇게 길어야만했나? 중간에 스펠링 틀려서 몇 번 고쳤다 😅

select a.company_code
     , a.founder
     , count(distinct b.lead_manager_code)
     , count(distinct c.senior_manager_code)
     , count(distinct d.manager_code)
     , count(distinct e.employee_code)
from company a
     left join lead_manager b on a.company_code = b.company_code
     left join senior_manager c on b.lead_manager_code = c.lead_manager_code
     left join manager d on c.senior_manager_code = d.senior_manager_code
     left join employee e on e.manager_code = d.manager_code
group by a.company_code, a.founder

• left join을 사용하면 예를 들어 직원이 없는 manager라도 count 할 수 있기 때문에 left join으로 수정해보았다. 
• 그럼 위와 같이 조인 조건이 많을 필요도 없음

출처