[HackerRank] Oracle - New Companies

혼자 풀었는가? group by 찾아보면서 해서 애매함..

해설은 안 봄 

 

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.

---

영어 울렁증으로 너무 길어서 문제 해석이 안되어 파파고 돌렸다 ㅠ.^

대충 회사명-창립자-리드 매니저 수 - 시니어 매니저 수 - 매니저 수 - 직원수 를 출력하면 된다.

 

테이블이 많이 제시되었는데, 사실 필요한건 Company (회사코드, 창립자)

Employee (회사코드, 상사들 코드... , 본인코드) 총 2개의 테이블이다.

 

처음 짠 코드

SELECT DISTINCT(COM.company_code), COM.founder, EM.lead, EM.senior, EM.manager, EM.employee
FROM Company COM
    LEFT JOIN (SELECT DISTINCT(C.company_code), 
            COUNT(DISTINCT C.lead_manager_code) OVER(PARTITION BY company_code) AS lead,
            COUNT(DISTINCT C.senior_manager_code) OVER(PARTITION BY company_code) AS senior,
            COUNT(DISTINCT C.manager_code) OVER(PARTITION BY company_code) AS manager,
            COUNT(DISTINCT C.employee_code) OVER(PARTITION BY company_code) AS employee
    FROM Employee C) EM
    ON COM.company_code = EM.company_code
ORDER BY COM.company_code;

    뭔가가 뭔가 하다.. GROUP BY 대신 PARTITION BY를 사용했는데 서브쿼리에서 필요한 정보를 모두 가공하고 창립자랑 매칭시키는 방법으로 문제를 풀었다.

 

그래서 조금 더 간결하게 다시 풀어보려고 한다.  

SELECT COM.company_code, 
        COM.founder,
        COUNT(DISTINCT C.lead_manager_code) AS lead,
        COUNT(DISTINCT C.senior_manager_code) AS senior,
        COUNT(DISTINCT C.manager_code) AS manager,
        COUNT(DISTINCT C.employee_code) AS employee
FROM Company COM
    LEFT JOIN Employee C
    ON COM.company_code = C.company_code
GROUP BY COM.company_code, COM.founder
ORDER BY COM.company_code;

PARTITION BY 구절을 생략하고, 각 매니저 및 직원의 count를 main에서 시도했다.

애초에 company_code로 그룹화했기 때문에 DISTINCT를 생략할 수 있었다!