Q)
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 2Explanation
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.
A)
문제는 다음과 같다.
Aber의 대기업 법인은 방금 몇 개의 새로운 회사를 인수했습니다. 각 회사는 이 계층 구조를 따릅니다
아래 테이블 스키마가 주어지면 회사_코드, 설립자 이름, 총 책임자 수, 총 선임 관리자 수, 총 관리자 수 및 총 직원 수를 출력하는 쿼리를 작성합니다. 회사_코드의 오름차순으로 출력 순서를 지정합니다.
테이블에 중복 레코드가 포함될 수 있습니다.
회사_코드는 문자열이므로 정렬은 숫자가 아니어야 합니다. 예를 들어, 회사 코드가 C_1, C_2, C_10 인 경우 오름차순 회사 코드는 C_1, C_10, C_2 가 됩니다.문제에 주어진 각 회사코드별로 직급별 인원수를 구하는 문제이다.
나는 Employee에 모든 정보가 담겨져 있기 때문에 전체 테이블을 조인하지 않고 Employee 테이블과 회사 정보가 담긴 Company 테이블을 이용하여 문제를 해결하였다.
우선 회사별로 각 직급별 인원을 조회해야 하기때문에 회사이름을 기준으로 GROUP BY 를 해줘야할 것 같아 다음과 같이 쿼리 작성을 이어갔다.
SELECT
C.COMPANY_CODE,C.FOUNDER
FROM
EMPLOYEE E
JOIN
COMPANY C
ON E.COMPANY_CODE = C.COMPANY_CODE
GROUP BY
E.COMPANY_CODE하지만 이처럼 쿼리를 작성하면 에러가 발생한다. 왜냐하면 GROUP BY 를 이용하면 SELECT 절에는 올 수 있는 항목은 다음과 같은 항목으로 제한된다.
- GROUP BY 로 잡은 기준이 되는 컬럼 ( 여기선 COMPANY_CODE )
- 다른 컬럼의
집계값( COUNT,MAX .. 등과 같은 집계값 ! )
따라서 FOUNDER 값을 조회하고자 하면 해당 값도 GROUP BY 절에 포함시켜줘야만 한다.
SELECT
C.COMPANY_CODE,C.FOUNDER
FROM
EMPLOYEE E
JOIN
COMPANY C
ON E.COMPANY_CODE = C.COMPANY_CODE
GROUP BY
C.COMPANY_CODE,C.FOUNDER이제 각 직급별 인원수를 COUNT 해야하고, 조건에 중복절이 존재할 수 있다고 하였으니 중복을 제거하여 인원을 COUNT 하기위해 DISTINCT, COUNT 함수를 사용하여 쿼리를 완성했다.
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
EMPLOYEE E
JOIN
COMPANY C
ON E.COMPANY_CODE = C.COMPANY_CODE
GROUP BY
C.COMPANY_CODE,C.FOUNDER
ORDER BY
C.COMPANY_CODE;해당 쿼리는 HackerRank 사이트에서 정상적으로 문제를 통과하여 문제가 없는 쿼리라고 생각하였지만 다른 분들이 작성한 풀이를 보고 이는 완벽한 쿼리가 아니라는 것을 알게되었다.
왜 위 쿼리가 잘못된 쿼리인지 여기서 사용한 테이블을 예를들어 확인해보자.
다음 2개의 테이블이 다음과 같다고 할 때 우리가 사용한 쿼리르 바탕으로 하나씩 계산해보자.
COMPANY TABLE :
EMPLOYEE TABLE :
두개의 테이블을 Employee 테이블을 기준으로 LEFT JOIN 한다면 다음과 같은 형태로 테이블이 구성될 것 이다.
| employee_code | manager_code | senior_manager_code | lead_manager_code | company_code | company_code | founder |
|---|---|---|---|---|---|---|
| E1 | M1 | SM1 | LM1 | C1 | C1 | Monika |
| E2 | M1 | SM1 | LM1 | C1 | C1 | Monika |
| E3 | M2 | SM3 | LM2 | C2 | C2 | Samantha |
| E4 | M3 | SM3 | LM2 | C2 | C2 | Samantha |
이러한 형태에서 이제 COMPANY_CODE, FOUNDER 를 기준으로 GROUP BY를 하고 각 직급별 사원수를 체크해보자
- COUNT(DISTINCT(E.LEAD_MANAGER_CODE))
C1 : 중복을 제거한 LM1 이 하나뿐이므로 --> 1
C2 : 중복을 제거한 LM2 이 하나뿐이므로 --> 1- COUNT(DISTINCT(E.SENIOR_MANAGER_CODE))
C1 : 중복을 제거한 SM1 이 하나뿐이므로 --> 1
C2 : 중복을 제거한 SM3 이 하나뿐이므로 --> 1- COUNT(DISTINCT(E.MANAGER_CODE)),
C1 : 중복을 제거한 M1 이 하나뿐이므로 --> 1
C2 : 중복을 제거한 M2,M3 로 2명이다 --> 2- COUNT(DISTINCT(E.EMPLOYEE_CODE))
C1 : 중복을 제거한 E1,E2 로 2명이므로 --> 2
C2 : 중복을 제거한 E3,E4 로 2명이다 --> 2따라서 다음과 같이 결과가 조회될거다.
C1 Monika 1 1 1 2
C2 Samantha 1 1 2 2아래는 문제에서 동일한 테이블을 사용하였을 때 조회되는 결과이다.
C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2왜 문제와 우리가 작성한 쿼리가 다른것일까 ?
Senior Manager Table:
이처럼 Senior Manager Table을 살펴보면 LM1 매니저 밑으로 SM1,SM2 두명이 존재하지만
우리가 사용한 Employee Table을 다시 살펴보면
EMPLOYEE TABLE :
이처럼 우리가 생각한것과 다른 형태로 테이블이 구성되어 있는것을 알 수 있다.
따라서 문제에서 주어진 결과를 정확하게 수행하려고 한다면 다음과 같이 쿼리를 수정하여야만한다.
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이처럼 문제에서 주어진 직급의 순서대로 차례차례 연결시켜 전체 테이블을 완성시켰고, 이를 통해 이전과 동일한 방법으로 각 직급별 사원수를 계산하였다.
