[MySQL/HackerRank] New Companies

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

New Companies | HackerRank

문제

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.

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
    JOIN LEAD_MANAGER L ON C.COMPANY_CODE = L.COMPANY_CODE
    JOIN SENIOR_MANAGER S ON C.COMPANY_CODE = S.COMPANY_CODE
    JOIN MANAGER M ON C.COMPANY_CODE = M.COMPANY_CODE
    JOIN EMPLOYEE E ON C.COMPANY_CODE = E.COMPANY_CODE
GROUP BY C.COMPANY_CODE, C.FOUNDER