[HackerRank] New Companies_여러 테이블 외부 조인, 그룹 별로 오름차순으로 보여주기

문제

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.

문제요약
요구하는 칼럼을 뽑아내되 company_code 오름차순 기준으로 나열하라.
테이블들은 중독된 records를 가지고 있다.
주의할 점 : company_code는 문자열이다. 그래서 숫자 분류가 불가능하다. 

 

풀이

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 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 1, 2
ORDER BY c.company_code ASC

/* 서브쿼리로 풀어보기(테이블 자체가 너무 커서 left join이 불가능할 때 사용) */
SELECT c.company_code, c.founder,
    (SELECT COUNT(DISTINCT lead_manager_code)
    FROM lead_manager lm
    WHERE c.company_code = lm.company_code),
    (SELECT COUNT(DISTINCT senior_manager_code)
    FROM senior_manager sm
    WHERE c.company_code = sm.company_code),
    (SELECT COUNT(DISTINCT manager_code)
    FROM manager m
    WHERE c.company_code = m.company_code),
    (SELECT COUNT(DISTINCT employee_code)
    FROM employee e
    WHERE c.company_code = e.company_code)
FROM company c
ORDER BY c.company_code ASC;

 

배운 점

Table을 문자로 지정해줬으면 SELECT문에서도 문자 지정해주기

Table 자체가 너무 크면 join대신 서브쿼리 사용하기