[HackerRank] 여러 개의 INNER JOIN : New Companies

1. 문제

 

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.

---

2. 답

 

• 답 1

SELECT c.company_code, c.founder
    , COUNT(DISTINCT(sm.lead_manager_code)) AS lead_mager_number
    , COUNT(DISTINCT(sm.senior_manager_code)) AS senior_manager_number
    , COUNT(DISTINCT(m.manager_code)) AS manager_number
    , COUNT(DISTINCT(e.employee_code)) AS employee_number 
FROM senior_manager AS sm 
    LEFT JOIN company AS c ON sm.company_code = c.company_code
    LEFT JOIN manager AS m ON sm.senior_manager_code = m.senior_manager_code
    LEFT JOIN employee AS e ON m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code

- 중간에 senior_manager table까지는 빠짐없이 각 데이터들이 모두 포함되어 있다고 생각하여

   senior_manager table에서 다른 table을 LEFT JOIN을 진행함

- 성공하긴 했지만, Company table에 있는 데이터가 senior_manager table에 없는 이러한 상황의 여러 testcase가
  있다면 실패할 코드

 

• 답 2 (정확한 답)

SELECT C.company_code
       , C.founder
       , COUNT(DISTINCT LM.lead_manager_code)
       , COUNT(DISTINCT SM.senior_manager_code)
       , COUNT(DISTINCT M.manager_code)
       , COUNT(DISTINCT E.employee_code)
FROM Company C
    LEFT JOIN Lead_Manager LM ON C.company_code = LM.company_code
    LEFT JOIN Senior_Manager SM ON LM. lead_manager_code = SM. lead_manager_code
    LEFT JOIN Manager M ON SM.senior_manager_code = M.senior_manager_code
    LEFT JOIN Employee E ON M.manager_code = E.manager_code
GROUP BY C.company_code, C. founder
ORDER BY C.company_code

- company table부터 다른 테이블들을 LEFT JOIN

- 모든 testcase에 통과하는 정확한 코드

---

3. KEY POINT

 

• 앞 테이블을 기준으로 LEFT JOIN 한다는 것이 앞 테이블에서 독자적으로 쓸 수 있다고 접근하면 안 됨.
  
  - C를 기준으로 JOIN 한다고 해서, JOIN 된 결과가 C로 대입되는 것이 아님 ex) C.lead_manager_code (X)
  - 합쳐진 테이블에서 독립적으로 접근해야 함 ex) LM.lead_manager_code (O)
• INNER JOIN 여러 개 사용 가능
• 데이터가 JOIN 할 때, 왼쪽 테이블에 있는 데이터가 오른쪽 테이블에는 없을 때와 같은 상황을 고려해야 하는지를
  판단하여 LEFT JOIN과 INNER JOIN을 판단해야 함.

---

 

www.hackerrank.com/challenges/the-company/problem?h_r=internal-search

New Companies | HackerRank