[HackerRank] New Companies

Q. Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:

Founder ->  Lead Manager -> Senior Manager -> Manager -> Employee

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.

Cloumn	Type
company_code	String
founder	String

• Lead_Manager: The lead_manager_code is the code of the lead manager, and the company_code is the code of the working company.

Cloumn	Type
lead_manager_code	String
company_code	String

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

Cloumn	Type
senior_manager_code	String
lead_manager_code	String
company_code	String

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

Cloumn	Type
manager_code	String
senior_manager_code	String
lead_manager_code	String
company_code	String

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

Cloumn	Type
employee_code	String
manager_code	String
senior_manager_code	String
lead_manager_code	String
company_code	String

---

Sample Input

---

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.

---

문제풀이

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 C
INNER JOIN Lead_Manager L ON C.company_code = L.company_code
INNER JOIN Senior_Manager S ON L.lead_manager_code = S.lead_manager_code
INNER JOIN Manager M ON S.senior_manager_code = M.senior_manager_code
INNER JOIN Employee E ON M.manager_code = E.manager_code
Group by C.company_code, C.founder
ORDER BY C.company_code

어려운 문제는 아니였지만 오랜만에 혼자힘으로 풀어본 문제이고, 해당 문제는 SELECT와 GROUPBY의 관계를 잘 생각해보고, JOIN을 어떻게 맺을지 생각해 보면 금방 풀 수 있는 문제같다

• 먼저 가장 위에 나오는 회사조직도를 잘 생각해 두어야한다. 조직도 순서대로 JOIN맺어야 하기 때문이다.
• 그리고 GROUP By절 에 나오지 않은 것을 SELECT에 넣기위해서는 집계함수의 도움이 필요하다. 우리는 사람의 수를 구해야 하기때문에 COUNT를 사용하기 때문에 문제가 없을것이다. 그리고 중복되지않게 명수를 입력해야하기 때문에 COUNT()절 안에 DISTINCT()를 넣어주면된다. GROUP BY에 없는 것 중 집계함수는 중첩하면 안되지만 DISTINCT() 는 집계함수가 아니기 때문에 유니크한값을 뽑고 COUNT로 갯수를 추출하면 된다.
• 그리고 회사와 founder를 기준으로 그룹핑을 하게되면 이후의 회사와 founder기준으로 명수를 표현할 수 있다.

보충할점

문제이해를 좀 더 빠르게 하고 원하는 바를 빠르게 캐치하여 문제를 해결하는것에서 만족하지 않고, 좀 더 빠르게 해결방법을 도출하는 연습을 하자!!