[HackerRank/SQL] New Companies

https://www.hackerrank.com/challenges/the-company/problem?h_r=next-challenge&h_v=zen 

New Companies | HackerRank

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 FormatExplanation
• 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.
• Sample Output
• C1 Monika 1 2 1 2 C2 Samantha 1 1 2 2
• 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_CODE가 같은 값을 기준으로 테이블을 조인할 것이다.

LEAD_MANAGER 테이블에서는 LEAD_MANAGER_CODE의 수를 COUNT한 값은 A1 컬럼에 저장할 것이고, SENIOR_MANAGER 테이블에서는 SENIOR_MANAGER_CODE의 수를, MANAGER 테이블에서는 MANAGER_CODE 수를, EMPLOYEE 테이블에서는 EMPLOYEE_CODE를 COUNT한 값을 각각 A2, A3, A4 컬럼에 저장할 것이다.

중복된 데이터가 있을 수 있다고 했으므로 COUNT할 때 DISTINCT를 붙여주어야 한다.

조인한 테이블에서 COMAPNY_CODE, FOUNDER, A1, A2, A3, A4 컬럼을 각각 조회한다.

COMPANY_CODE 순으로 값을 조회할 것이므로 ORDER BY 절에 COMPANY_CODE를 작성한다.

 

[코드]

SELECT C.COMPANY_CODE, C.FOUNDER, LM.A1, SM.A2, M.A3, E.A4
FROM COMPANY C 
    JOIN(SELECT COMPANY_CODE, COUNT(DISTINCT LEAD_MANAGER_CODE) AS A1
    FROM LEAD_MANAGER
    GROUP BY COMPANY_CODE ) LM
    ON LM.COMPANY_CODE = C.COMPANY_CODE
    JOIN
    (SELECT COMPANY_CODE, COUNT(DISTINCT SENIOR_MANAGER_CODE) AS A2
    FROM SENIOR_MANAGER
    GROUP BY COMPANY_CODE ) SM
    ON SM.COMPANY_CODE = C.COMPANY_CODE
    JOIN(SELECT COMPANY_CODE, COUNT(DISTINCT MANAGER_CODE) AS A3
    FROM MANAGER
    GROUP BY COMPANY_CODE ) M
    ON M.COMPANY_CODE = C.COMPANY_CODE
    JOIN(SELECT COMPANY_CODE, COUNT(DISTINCT EMPLOYEE_CODE) AS A4
    FROM EMPLOYEE
    GROUP BY COMPANY_CODE ) E 
    ON E.COMPANY_CODE = C.COMPANY_CODE
    
ORDER BY C.COMPANY_CODE