New Companies / HackerRank, SQL, MySQL

문제

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy (See Fig1):

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.

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.

Fig 1

Fig 2 - table company

Fig 3 - table lead_manager

Fig 4 - table senior_manager

Fig 5 - table manager

Fig 6 - table company

source : HackerRank

 

제출답안(MySQL)

-- query company_code, founder name, total # of lead m, senior m, m, e ODBY 1
SELECT c.company_code, c.founder, 
    COUNT(DISTINCT(l.lead_manager_code)), 
    COUNT(DISTINCT(s.senior_manager_code)),
    COUNT(DISTINCT(m.manager_code)),
    COUNT(DISTINCT(e.employee_code))

FROM company AS c
    LEFT JOIN lead_manager AS l ON c.company_code = l.company_code
    LEFT JOIN senior_manager AS s ON c.company_code = s.company_code
    LEFT JOIN manager AS m ON c.company_code = m.company_code
    LEFT JOIN employee AS e ON c.company_code = e.company_code

GROUP BY c.company_code, c.founder
ORDER BY 1

 

 

 

풀이(MySQL)

각 코드의 고유값 카운트를 위해 COUNT(DISTINCT(각 코드))

-- query company_code, founder name, total # of lead m, senior m, m, e ODBY 1
SELECT c.company_code, c.founder, 
    COUNT(DISTINCT(l.lead_manager_code)), 
    COUNT(DISTINCT(s.senior_manager_code)),
    COUNT(DISTINCT(m.manager_code)),
    COUNT(DISTINCT(e.employee_code))

 

각 테이블을 연결하는 칼럼인 company code를 선택하여 JOIN

>> Inner Join을 할 경우 누락될 경우가 있기 때문에 쿼리의 기준이 되는 company 테이블을 기준으로 LEFT JOIN

FROM company AS c
    LEFT JOIN lead_manager AS l ON c.company_code = l.company_code
    LEFT JOIN senior_manager AS s ON c.company_code = s.company_code
    LEFT JOIN manager AS m ON c.company_code = m.company_code
    LEFT JOIN employee AS e ON c.company_code = e.company_code

 

company_code와 founder를 기준으로 데이터를 그룹화 후 company_code 기준 정렬

 

GROUP BY c.company_code, c.founder
ORDER BY 1

 

 

 

문제 바로가기(MySQL)