[HackerRank](M) New Companies

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true 

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

 

 

[답]

- mysql

select c.company_code, c.founder, l.lead_manager, s.senior_manager, m.manager, e.employee
from company c
inner join (select company_code, count(distinct(lead_manager_code)) lead_manager 
from lead_manager group by company_code) l on c.company_code = l.company_code
inner join (select company_code, count(distinct(senior_manager_code)) senior_manager 
from senior_manager group by company_code) s on c.company_code = s.company_code
inner join (select company_code, count(distinct(manager_code)) manager 
from manager group by company_code) m on c.company_code = m.company_code
inner join (select company_code, count(distinct(employee_code)) employee 
from employee group by company_code) e on c.company_code = e.company_code
order by c.company_code

 

문제가 길지만 해석해보면 각각의 테이블이 있는데 그 중 company_code, founder, lead_manager의 수, senior_manager의 수, manager의 수, employee의 수 를 구해주고 정렬은 company_code로 오름차순 정렬한다. 

테이블이 5개를 확인해봤는데 안에 company_code의 컬럼은 모두 존재한다. 그래서  이를 모두 inner join을 시켜주었다.

inner join 두개는 해봤어요 5개를 해본 건 처음이다.

몇 개가 되던 똑같은 문법으로 사용해주었고, 숫자를 세주는 count에서 distinct를 해준 이유는 중복 데이터가 존재할 수도 있기 때문이다.

그리고 마지막 정렬은 company_code로 해주면 끝이다.