[HackerRank] New Companies - MySQL 풀이 (JOIN할 때 우리가 놓칠 수 있는 것)

안녕하세요, 준브로입니다. 

 

 

SQL을 이용하여 테이블을 JOIN할 때, 

LEFT JOIN을 해야 할지, INNER JOIN을 해야 할지 고민되는 순간이 있습니다. 

 

 

오늘은 해커랭크의 Medium 난이도의 문제인 'New Companies'를 풀어보면서 고민에 대한 인사이트를 얻어보도록 하겠습니다. 

---

문제 설명

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

---

문제 해석

Amber의 대기업이 인수한 몇 개의 새 회사들에 대한 계층도를 보여줍니다. 

Founder -> Lead Manager -> Senior manager -> Manager -> Employee 로 구성되어 있습니다. 

 

- company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees를 구하는 쿼리를 작성하면 됩니다.

- 정렬 조건은 company_code에 대한 오름차순

 

 참고 사항:

- 테이블에 중복된 레코드가 존재할 수 있음 

- company_code는 숫자(numeric)가 아닌, 문자열(string)이기 때문에 숫자 크기 순으로 정렬되는 것이 아님. (예를 들어, C1, C2, ..., C10이 아닌 C1, C10, ..., C2)

 

문제 해결 방법

주어진 테이블은 모두 5개입니다. 

그러면, 우리가 구해야 할 정보는 무엇이며, 정렬 조건은 어떻게 되는지 살펴보겠습니다! 

 

 

구해야 할 것: company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees

 

정렬 조건: company_code ASC(오름차순 정렬)

 

구해야 할 정보만 따져본다면, Employee 테이블에 모든 code 정보들이 있으므로, 

"company table 과 Employee table 두 개만 JOIN해서 문제를 해결할 수 있겠네?" 라고 생각하셨을 수도 있습니다. 

이렇게 해결하게 되면 문제는 간단하게 풀릴 수 있고, 정답이 나올 수도 있습니다! 

 

 

하지만, 정답이 되기 위해서는 다음과 같은 전제 조건이 필요합니다. 

'employee를 두고 있지 않은 manager가 한 명도 없을때' 

 

 

예를 들어, 

다음과 같은 C1 회사를 볼 때, 매니저 M1은 직원 E1, E2를 관리하지만, 시니어 매니저 S2 - 매니저 M2 밑으로는 직원이 존재하지 않을 가능성도 있는 것입니다. 실제로도 작은 규모의 스타트업의 경우 최소한의 인원으로, employee 직급 자체가 존재하지 않을 수도 있죠. 

 

C1	Junbro	L1	S1	M1	E1, E2
			S2	M2	X

 

만약, 모든 계층에서 null이 존재하지 않는다면, 위 처럼 간단하게 두 테이블만 JOIN하여 문제를 해결할 수 있습니다. 

하지만, 우리가 풀려는 문제에서는 확실한 언급이 없기 때문에, 추후에 발생할 수 있는 실수를 방지하기 위해 모든 테이블을 JOIN하여 문제를 풀어보는 것이 좋습니다. 

 

INNER JOIN / LEFT JOIN 헷갈릴 때

그래서 모든 테이블을 JOIN해야 한다는 것은 알았는데, INNER 와 LEFT 중 어떤 JOIN을 해야할까요?

 

앞서 언급했던 C1 회사의 예시와 구해야할 것에 대해 다시 한번 살펴보겠습니다. 

C1	Junbro	L1	S1	M1	E1, E2
			S2	M2	X

company_code 별로 Founder의 이름과, 각각 계층을 담당하는 수를 집계해야 하기 때문에, null이 존재할 수 있는 모든 경우를 고려해야 합니다. 즉, LEFT JOIN을 사용해야 하는 것이죠! 

 

INNER JOIN을 사용하게 된다면 null이 존재할 수 없는 교집합(intersection)만을 출력하여 원하는 정답을 얻지 못할 가능성이 존재하기 때문입니다. 

 

문제 풀이

구해야 할 것: company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees

정렬 조건: company_code ASC(오름차순 정렬)

위 사항을 모두 만족시키면서, LEFT JOIN을 사용한 쿼리문은 다음과 같습니다. 

SELECT company.company_code
     , company.founder
     , COUNT(DISTINCT lead_manager.lead_manager_code) AS 'total number of lead managers'
     , COUNT(DISTINCT senior_manager.senior_manager_code) AS 'total number of senior managers'
     , COUNT(DISTINCT manager.manager_code) AS 'total number of managers'
     , COUNT(DISTINCT employee.employee_code) AS 'total number of employees'
FROM company
    LEFT JOIN lead_manager ON company.company_code = lead_manager.company_code
    LEFT JOIN senior_manager ON lead_manager.lead_manager_code = senior_manager.lead_manager_code
    LEFT JOIN manager ON senior_manager.senior_manager_code = manager.senior_manager_code
    LEFT JOIN employee ON manager.manager_code = employee.manager_code
GROUP BY company.company_code
	     , company.founder
ORDER BY company.company_code

중복 값을 제외시키기 위해 COUNT DISTINCT를 사용하였고, company_code와 founder로 GROUP BY 해줬습니다. 

 

정답으로 바르게 출력이 되었으며, 제출을 완료해 주시면 됩니다. 

 

이 문제는 INNER JOIN으로 풀거나, Company + Employee 테이블만의 JOIN으로 풀어도 정답이 나오긴 합니다. 

 

하지만, 추후에 실수를 하지 않기 위해선 모든 테이블을 다루면서 적절히 INNER / LEFT JOIN을 활용하여 고민하면서 문제를 푸는 연습을 하는 것이 필요하겠다는 교훈을 주는 문제였습니다. (왼쪽에는 데이터가 있는데, 오른쪽에는 데이터가 있을지 없을지를 고민해보는 것부터 시작하자!)

 

 

이상으로 포스팅을 마치며, 다음에도 코딩테스트 문제로 찾아뵙겠습니다. 

감사합니다:)