HackerRank SQL - 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 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.

Problem

company 테이블에서 company code와 founder, lead manger의 수, senier manager의 수, employee의 수를 표기하라.

단 중복이 존재 할 수 있으며 회사명을 기준으로 오름차순 정렬하라. (회사명이 숫자타입이 아니므로 주의)

---

Answer1

SELECT COM.company_code, COM.founder, A.cnt, B.cnt, C.cnt, D.cnt
FROM
COMPANY COM
,
(SELECT C.COMPANY_CODE, COUNT(DISTINCT LM.lead_manager_code) as cnt
FROM COMPANY C, Lead_Manager LM
WHERE C.company_code = LM.company_code
GROUP BY C.COMPANY_CODE) A
,
(SELECT C.COMPANY_CODE, COUNT(DISTINCT SM.senior_manager_code) as cnt
FROM COMPANY C, Senior_Manager SM
WHERE C.company_code = SM.company_code
GROUP BY C.COMPANY_CODE) B
,
(SELECT C.COMPANY_CODE, COUNT(DISTINCT M.manager_code) as cnt
FROM COMPANY C, Manager M
WHERE C.company_code = M.company_code
GROUP BY C.COMPANY_CODE) C
,
(SELECT C.COMPANY_CODE, COUNT(DISTINCT E.employee_code) as cnt
FROM COMPANY C, Employee E
WHERE C.company_code = E.company_code
GROUP BY C.COMPANY_CODE) D
WHERE A.company_code = B.company_code
  AND B.company_code = C.company_code
  AND C.company_code = D.company_code
  AND COM.company_code = A.company_code
ORDER BY 
      COM.company_code ASC

분명 이렇게 풀라고 만든 문제는 아닐 것이다....

다른 방법을 찾고 싶다. 아마도 inner join 에서 시간이 오래 걸리는 것 같은데 책을 좀 찾아봐야겠다.

 

 

---

How to solve

우선 제일 쉬운 무지성급 접근방식은 서브쿼리로 정리 해두고 마지막에 inner join으로 마무리하는 방식이다.

어쨌든 테이블이 분리되어 있으니, 이 방법이 제일 효율적일 수도 있겠다는 생각이 든다. (물론 아니겠지....)

 

어쨌든 먼저 lead_manager 테이블로 먼저 시도해본다.

SELECT C.COMPANY_CODE, COUNT(DISTINCT LM.lead_manager_code) as cnt
FROM COMPANY C, Lead_Manager LM
WHERE C.company_code = LM.company_code
GROUP BY C.COMPANY_CODE

중복이 있을 수 있으니 distinct도 걸어주고 inner join해본다.

 

SELECT COM.company_code, COM.founder, A.cnt, B.cnt, C.cnt, D.cnt
FROM
COMPANY COM
,
(SELECT C.COMPANY_CODE, COUNT(DISTINCT LM.lead_manager_code) as cnt
FROM COMPANY C, Lead_Manager LM
WHERE C.company_code = LM.company_code
GROUP BY C.COMPANY_CODE) A
,
(SELECT C.COMPANY_CODE, COUNT(DISTINCT SM.senior_manager_code) as cnt
FROM COMPANY C, Senior_Manager SM
WHERE C.company_code = SM.company_code
GROUP BY C.COMPANY_CODE) B
,
(SELECT C.COMPANY_CODE, COUNT(DISTINCT M.manager_code) as cnt
FROM COMPANY C, Manager M
WHERE C.company_code = M.company_code
GROUP BY C.COMPANY_CODE) C
,
(SELECT C.COMPANY_CODE, COUNT(DISTINCT E.employee_code) as cnt
FROM COMPANY C, Employee E
WHERE C.company_code = E.company_code
GROUP BY C.COMPANY_CODE) D
WHERE A.company_code = B.company_code
  AND B.company_code = C.company_code
  AND C.company_code = D.company_code
  AND COM.company_code = A.company_code
ORDER BY 
      CAST(SUBSTR(COM.company_code FROM 2 FOR 2) AS UNSIGNED) ASC

이렇게 정렬하는게 깔끔한데 문제에서는 단순히 company_code로 오름차순 표기를 원했다.

 

 

 

4성 장군이 되었다!