[HackerRank] New Companies Solution (풀이)

new companies 문제 바로가기 

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.

- 각 회사코드, 창립자이름, 리드매니저 총수, 시니어 매니저 총수, 매니저 총수, 직원 총수를 프린트하는 쿼리 작성
- 회사코드로 내림차순 정렬 
- 레코드는 중복될 수 있음
- 회사코드는 숫자가 아닌 문자  
   C_1,C_10, C_2 순서로 정렬되어야 함 

 

[풀이]

SELECT co.company_code
      ,co.founder
      ,COUNT(DISTINCT lm.lead_manager_code)
      ,COUNT(DISTINCT sm.senior_manager_code)
      ,COUNT(DISTINCT ma.manager_code)
      ,COUNT(DISTINCT em.employee_code)
FROM company co
     LEFT JOIN lead_manager lm ON lm.company_code = co.company_code
     LEFT JOIN senior_manager sm ON sm.company_code = lm.company_code
     LEFT JOIN manager ma ON ma.company_code = sm.company_code
     LEFT JOIN employee em ON em.company_code = ma.company_code
GROUP BY co.company_code ,co.founder
ORDER BY co.company_code