MySQL | HackerRank | New Companies

Problem

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.

---

요약

• 회사별로 회사코드, 창립자 이름, 리드 매니저 수, 시니어 매니저 수, 매니저 수, 직원 수 나타내기
• 테이블에 중복된 레코드가 존재할 수 있다
• 회사 코드는 Integer 형식이 아닌 String 이므로 Sort 할 경우 1, 2, 3... 의 순서가 아닌 1, 10, 100, 2...  형식으로 Sort 된다

접근 방법

• 테이블 계층에 따라 상위 테이블의 코드가 있다 > JOIN Key값이 존재한다
• 테이블에 중복된 레코드가 존재한다 > Distinct로 중복 제거가 필요하다
• 테이블 데이터들을 집계하여 수치화 한다 > 집계 함수를 사용, 이때 집계 함수와 단일행을 같이 보고 싶으면 GROUP BY(단일행 등 기준 컬럼)을 사용한다

 

---

문제 풀이

SELECT distinct a.company_code
     , a.founder
     , COUNT(distinct b.lead_manager_code)
     , COUNT(distinct b.senior_manager_code)
     , COUNT(distinct b.manager_code)
     , COUNT(distinct b.employee_code)
FROM Company as a
    LEFT JOIN Employee as b
    ON a.company_code = b.company_code
GROUP BY a.company_code, a.founder
ORDER BY a.company_code

처음에 위 코드로 접근하여 값을 냈지만,
계층 별 테이블을 모두 사용하지 않는다면 놓치는 데이터 로우가 발생할 수 있을 것이고 문제 의도와 다른 풀이라고 생각

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 
            AND l.lead_manager_code = s.lead_manager_code
    LEFT JOIN Manager as m
        ON c.company_code = m.company_code 
            AND l.lead_manager_code = m.lead_manager_code
            AND s.senior_manager_code = m.senior_manager_code
    LEFT JOIN Employee as e
        ON c.company_code = e.company_code 
            AND l.lead_manager_code = e.lead_manager_code
            AND s.senior_manager_code = e.senior_manager_code
            AND m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code

이마저도 코드가 너무 길어져서 좀 더 간결하게 작성할 수 있는 방법은 없을지 검색

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
     , Lead_Manager l
     , Senior_Manager s
     , Manager m
     , Employee e
WHERE c.company_code = l.company_code 
      AND l.lead_manager_code = s.lead_manager_code
      AND s.senior_manager_code = m.senior_manager_code
      AND m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code

JOIN 없이 WHERE 절만을 이용한 문제 풀이도 가능하다

이 밖에도 JOIN 시 테이블을 직접 선언하지 않고 Sub Query를 이용하기도 하는 등 여러 문제 풀이들을 볼 수 있었다

---

출처

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

New Companies | HackerRank

https://techblog-history-younghunjo1.tistory.com/163

[SQL] 연속적인 JOIN 구문으로 해결하기(HackerRank - New Companies 문제)