New Companies

www.hackerrank.com/domains/sql

 

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.

---

 

번역 : company 별로 lead_manager,senior_manager,manager,employee의 명수를 출력하라. 

풀이 : 테이블을 자세히 들여다 보면 employee 테이블이 모든 code를 다 가지고 있다.

 

번역과 풀이만 보면 참 쉽지만 생각보다 오래걸렸음.

왜 실패했는지 안나오기 때문에 Discussions의 도움을 받았음.

단순히 기대값과 결과값에 의존하지 않고 문제 자체를 이해하고 테이블 구조를 파악하는 것이 관건인듯?

역시 medium..

 

실패사례

 

1. inner join 사용

SELECT T1.COMPANY_CODE,FOUNDER, COUNT(distinct T2.lead_manager_code),COUNT(distinct T3.Senior_Manager_CODE),COUNT(distinct T4.Manager_CODE),COUNT(distinct T5.Employee_CODE) FROM Company AS T1 INNER JOIN Lead_Manager AS T2 ON T1.COMPANY_CODE = T2.COMPANY_CODE INNER JOIN Senior_Manager AS T3 ON T2.Lead_Manager_CODE = T3.Lead_Manager_CODE INNER JOIN Manager AS T4 ON T3.Senior_Manager_CODE = T4.Senior_Manager_CODE INNER JOIN Employee AS T5 ON T4.Manager_CODE = T5.Manager_CODE GROUP BY  T1.COMPANY_CODE,FOUNDER ORDER BY LEN(T1.COMPANY_CODE) ASC , T1.COMPANY_CODE ASC 결과값 C1         MONIKA     1 1 1 2 C2         SAMANTHA   1 1 2 2 기대값 C1         MONIKA     1 1 1 2  C2         SAMANTHA   1 1 2 2

테스트 데이터로는 기대값과 결과값이 같았는데 이상하게 테스트 케이스를 못넘겼음.

 

왜...왜죠?

 

너무 길고 시간이 오래걸려서 그런걸 수도 있다고 생각해서 담백하게 짜보기로 함.

 

 

2. select 절에서 subquery 이용

SELECT COMPANY_CODE , FOUNDER,         (SELECT COUNT(*) FROM Lead_Manager WHERE COMPANY_CODE = T1.COMPANY_CODE),         (SELECT COUNT(*) FROM Senior_Manager WHERE COMPANY_CODE = T1.COMPANY_CODE),         (SELECT COUNT(*) FROM Manager WHERE COMPANY_CODE = T1.COMPANY_CODE),         (SELECT COUNT(*) FROM Employee WHERE COMPANY_CODE = T1.COMPANY_CODE)  FROM COMPANY AS T1  ORDER BY LEN(COMPANY_CODE) ASC , COMPANY_CODE ASC  결과값 C1         MONIKA     1 2 1 2  C2         SAMANTHA   1 1 2 2 기대값 C1         MONIKA     1 1 1 2  C2         SAMANTHA   1 1 2 2

기대값과 결과값이 다름. 결과값을 보아하니 테이블 끼리 연결이 안되고 있어서 각 테이블 별로 grouping 하여 갯수만 count 된듯.

 

EMPLOYEE_CODE / MANAGER_CODE /SENIOR_MANAGER_CODE / LEAD_MANAGER_CODE / COMPANY_CODE

---------------------------------------------------------------------------------------------------------------------
E1                             M1                       SM1                             LM1                         C1        
E2                             M1                       SM1                             LM1                         C1        

 

예를들어 employee 테이블을 기준으로 보면 c1의 employee는 2명(E1,E2) 이와 연결된MANAGER,SENIOR_MANAGER,LEAD_MANAGER는 각각 한명씩이다.

 

그런데 SUBQUERY를 이용했을때 결과 값은 SENIOR_MANAGER가 두명으로 나오는데, 이것은 단순히 C1의 SENIOR_MANAGER가 두명(SM1,SM3)인 것만 COUNT 한듯?

 

SENIOR_MANAGER_CODE / LEAD_MANAGER_CODE / COMPANY_CODE

---------------------------------------------------------------------------------
SM1                                    LM1                          C1        
SM2                                    LM1                          C1        

 

 

핵심은 모든 코드가 존재하는 마지막 테이블인 EMPLOYEE 테이블.

EMPLOYEE 테이블을 위주로 쿼리를 짜보자!

 

SELECT C.COMPANY_CODE, C.FOUNDER,        COUNT(DISTINCT E.LEAD_MANAGER_CODE),        COUNT(DISTINCT E.SENIOR_MANAGER_CODE),        COUNT(DISTINCT E.MANAGER_CODE),        COUNT(DISTINCT E.EMPLOYEE_CODE) FROM COMPANY AS C INNER JOIN EMPLOYEE AS E ON E.COMPANY_CODE = C.COMPANY_CODE GROUP BY C.COMPANY_CODE, C.FOUNDER ORDER BY C.COMPANY_CODE 결과값 C1         MONIKA     1 1 1 2 C2         SAMANTHA   1 1 2 2 기대값 C1         MONIKA     1 1 1 2 C2         SAMANTHA   1 1 2 2

 

성공...왈칵!

교훈은 COUNTING 할때 어떤 테이블을 기준으로 진행하느냐가 중요하다는 것!