해커랭크_New Companies

• 문제 point : 우선 너무 길어서 핵심 파악이 힘듦. 결국 COMPANY_CODE, FOUNDER 컬럼 기준으로 각 직급 직원 수 집계(중복 제거) 하기
  • COMPANY, EMPLOYEE 테이블 두개만 연결해도 됨!
  • 중복 제거가 포인트

SELECT A.company_code
    , A.founder
    , COUNT(DISTINCT B.lead_manager_code) AS lead_manager_cnt  
    , COUNT(DISTINCT B.senior_manager_code) AS senior_manager_cnt 
    , COUNT(DISTINCT B.manager_code) AS manager_cnt  
    , COUNT(DISTINCT B.employee_code) AS employee_cnt  
FROM company A
INNER JOIN employee B
ON A.company_code = B.company_code
GROUP BY A.company_code
    , A.founder
order by A.company_code ASC