[SQL : select 서브쿼리, join ] HackerRank : New Companies

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.

Input Format

The following tables contain company data:

• Company: The company_code is the code of the company and founder is the founder of the company. 

• Lead_Manager: The lead_manager_code is the code of the lead manager, and the company_code is the code of the working company. 

• Senior_Manager: The senior_manager_code is the code of the senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company. 

• Manager: The manager_code is the code of the manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company. 

• Employee: The employee_code is the code of the employee, the manager_code is the code of its manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company. 

---

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.

 

>> 문제 해결 코드

 

1. 잘못된 코드 : 내가 처음에 접근했던 방식

select c.company_code,  c.founder , count(distinct e.lead_manager_code) , 
		count(distinct e.senior_manager_code),
        count(distinct e.manager_code),count(distinct e.employee_code)
        
from Employee as e
    inner join Company as c on e.company_code = c.company_code 
    
group by c.company_code, c.founder
order by c.company_code

 

: 사실, 이런식으로 구현하려고 했지만, 계속 갯수가 동일하게 나오는 문제가 발생했다. 그 이유는 distinct라는 중복을 제거해주지 않아서였다... distinct 가 없어서 오류가 나온것이었다.

 두번째 에러는 c.founder 을 groupby에 넣지 않고 select에 넣으려고 했더니 오류가 발생하는 것이었다. 집계함수를 안쓸때는 다 groupby에 넣어준것을 잊지 말아야겠다. 

 

하지만, 위의 코드는 employee table이 모든 값을 담고 있다는 전제하에 가능한 것이다. 만약, 리드 매니저 아래 부하직원이 없다면, 전체 count를 세는데 오류가 발생했을 것이다. 

 

 

 

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 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

 

:  생각지도 못했던, 코드 풀이었는데,, left join 을 해주어서 모든 경우를 다 살필 수 있게 해주는 것이다

 count 의 distinct 외우자! 

 

 

3. 또 다른 풀이 ( Select subquery ) 를 이용한 풀이 

SELECT c.company_code
        , c.founder
        , ( SELECT COUNT(DISTINCT lead_manager_code)
            FROM Lead_Manager
            WHERE company_code = c.company_code)
        , ( SELECT COUNT(DISTINCT senior_manager_code)
        FROM Senior_Manager
        WHERE company_code = c.company_code)
        , ( SELECT COUNT(DISTINCT manager_code)
        FROM Manager
        WHERE company_code = c.company_code)
         , ( SELECT COUNT(DISTINCT employee_code)
        FROM Employee
        WHERE company_code = c.company_code)
FROM Company c
ORDER BY company_code

 

 

 

www.hackerrank.com/challenges/the-company/problem

New Companies | HackerRank