SQL 기초(8) - 다중 Join

hackerrank - New companies

새로운 회사를 인수합병하였다. 해당 회사는 아래와 같은 구조의 직원서열이 존재한다.

Founder
⬇️
Lead Manager
⬇️
Senior Manager
⬇️
Manager
⬇️
Employee

아래의 테이블 구조를 참고하여, company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees를 반환하는 SQL문을 작성하시오.(Company_code로 오름차순 정렬)

select C.company_code,
C.founder, 
count(distinct LM.lead_manager_code), #자꾸 파이썬 cnt랑 햇갈렸음
count(distinct SM.senior_manager_code), #distinct로 갯수 세기
count(distinct M.manager_code),
count(distinct E.employee_code)
from company C 
left join lead_manager LM on C.company_code=LM.company_code
left join senior_manager SM on LM.lead_manager_code = SM.lead_manager_code
left join manager M on SM.senior_manager_code = M.senior_manager_code
left join Employee E on M.manager_code = E.manager_code
group by C.company_code, C.founder
order by C.company_code ASC;