Hackerrank / New Companies

링크 : https://www.hackerrank.com/challenges/the-company/problem

 

Sample Input

Company Table: 

 Lead_Manager Table: 

 Senior_Manager Table: 

 Manager Table: 

 Employee Table: 

 

Sample Output

C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2

 

Explanation

In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1. There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under manager M1.

In company C2, the only lead manager is LM2. There is one senior manager, SM3, under LM2. There are two managers, M2 and M3, under senior manager SM3. There is one employee, E3, under manager M2, and another employee, E4, under manager, M3.

 

Solution

SELECT distinct 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 ((select * from Company)A inner join (select * from Lead_Manager) B
    on A.company_code=B.company_code
    inner join (select lead_manager_code, senior_manager_code from Senior_Manager) C
    on B.lead_manager_code=C.lead_manager_code
    left outer join (select senior_manager_code, manager_code from Manager) D
    on C.senior_manager_code=D.senior_manager_code
    left outer join (select manager_code, employee_code from Employee) E
    on D.manager_code=E.manager_code
    )
GROUP BY A.company_code, A.founder

Inner Join, Left Outer Join 을 활용하여 문제를 해결하였다.

group by와 Count 함수 사용.