HackerRank MySQL - New Companies
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.
- SELECT C.COMPANY_CODE,
- C.FOUNDER,
- (SELECT COUNT(DISTINCT LEAD_MANAGER_CODE)
- FROM LEAD_MANAGER L
- WHERE L.COMPANY_CODE = C.COMPANY_CODE),
- (SELECT COUNT(DISTINCT SENIOR_MANAGER_CODE)
- FROM SENIOR_MANAGER S
- WHERE S.COMPANY_CODE = C.COMPANY_CODE),
- (SELECT COUNT(DISTINCT MANAGER_CODE)
- FROM MANAGER M
- WHERE M.COMPANY_CODE = C.COMPANY_CODE),
- (SELECT COUNT(DISTINCT EMPLOYEE_CODE)
- FROM EMPLOYEE E
- WHERE E.COMPANY_CODE = C.COMPANY_CODE)
- FROM COMPANY C
- ORDER BY C.COMPANY_CODE ASC;
