SQL 코드카타 169 - New Companies

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

New Companies | HackerRank

 

• Company: company_code는 회사의 코드이고, founder는 회사의 창립자입니다.

Column	Type
company_code	String
founder	String

• Lead_Manager: lead_manager_code는 리드 매니저의 코드이고, company_code는 그 리드 매니저가 일하는 회사의 코드입니다.

Column	Type
lead_manager_code	String
company_code	String

• Senior_Manager: senior_manager_code는 시니어 매니저의 코드, lead_manager_code는 그 시니어 매니저의 리드 매니저 코드, company_code는 그 시니어 매니저가 일하는 회사의 코드입니다.

Column	Type
senior_manager_code	String
lead_manager_code	String
company_code	String

• Manager: manager_code는 매니저의 코드, senior_manager_code는 그 매니저의 시니어 매니저 코드, lead_manager_code는 그 매니저의 리드 매니저 코드, company_code는 그 매니저가 일하는 회사의 코드입니다.

Column	Type
manager_code	String
senior_manager_code	String
lead_manager_code	String
company_code	String

• Employee: employee_code는 직원의 코드, manager_code는 그 직원의 매니저 코드, senior_manager_code는 그 직원의 시니어 매니저 코드, lead_manager_code는 그 직원의 리드 매니저 코드, company_code는 그 직원이 일하는 회사의 코드입니다.

Column	Type
employee_code	String
manager_code	String
senior_manager_code	String
lead_manager_code	String
company_code	String

 

다음 테이블 스키마를 기준으로 쿼리를 작성하여 회사 코드(company_code), 창립자 이름(founder name), 총 리드 매니저 수(total number of lead managers), 총 시니어 매니저 수(total number of senior managers), 총 매니저 수(total number of managers), 그리고 총 직원 수(total number of employees)를 출력하세요. 결과를 회사 코드로 오름차순 정렬하세요.

참고:

• 테이블에 중복된 레코드가 포함될 수 있습니다.
• company_code는 문자열이므로, 정렬 시 숫자가 아닌 문자열 정렬을 사용해야 합니다. 예를 들어, company_code가 C_1, C_2, C_10인 경우, 오름차순 정렬 결과는 C_1, C_10, C_2가 됩니다.

 

처음에는 with 구문을 사용해서 인원의 수를 테이블별로 각각 셀 생각이었지만 이 사이트의 sql버전이 with를 지원하지 않았기 때문에 보기에는 좀 복잡해보여도 서브쿼리를 사용하기로 했다.

 

select
    company_code,
    count(distinct lead_manager_code) AS total_lead_managers
from Lead_Manager
group by company_code) L on C.company_code = L.company_code

 

with를 사용할 수 없기 때문에 모든 조건들을 각각 서브쿼리로 만들어서 company테이블과 결합해주어야한다. 가장 먼저 Lead_Manager의 수를 센다.

 

select *
from
    Company C
left join
(select
    company_code,
    count(distinct lead_manager_code) AS total_lead_managers
from Lead_Manager
group by company_code) L on C.company_code = L.company_code

 

서브쿼리를 company와 결합한다. 같은 방법으로 머지 테이블도 반복한다.

 

left join
(select
    company_code,
    count(distinct senior_manager_code) AS total_senior_manager
from Senior_manager
group by company_code
) S on  C.company_code = S.company_code

Senior_Manager

 

left join
(select
    company_code,
    count(distinct manager_code) AS total_manager
from Manager
group by company_code
) M on  C.company_code = M.company_code

Manager

 

left join
(select
    company_code,
    count(distinct employee_code) AS total_company
from Employee
group by company_code
) E on  C.company_code = E.company_code

Employee

 

select
    C.company_code,
    C.founder,
    coalesce(L.total_lead_managers, 0),
    coalesce(S.total_senior_manager, 0),
    coalesce(M.total_manager, 0),
    coalesce(E.total_company, 0)
from
    Company C
left join
(select
    company_code,
    count(distinct lead_manager_code) AS total_lead_managers
from Lead_Manager
group by company_code) L on C.company_code = L.company_code
left join
(select
    company_code,
    count(distinct senior_manager_code) AS total_senior_manager
from Senior_manager
group by company_code
) S on  C.company_code = S.company_code
left join
(select
    company_code,
    count(distinct manager_code) AS total_manager
from Manager
group by company_code
) M on  C.company_code = M.company_code
left join
(select
    company_code,
    count(distinct employee_code) AS total_company
from Employee
group by company_code
) E on  C.company_code = E.company_code
order by C.company_code

 

모든 서브쿼리를 결합했다면 필요한 데이터를 고른 후 company_code에 대해서 오름차순정렬을 하면 된다.

 

문제 자체는 어렵지 않았지만 문제가 너무 길고 테이블이 많아서 코드 작성할때 헷갈리는 점이 있었다.