We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
WITH C AS(
SELECT COMPANY_CODE,FOUNDER FROM COMPANY ORDER BY COMPANY_CODE),
E AS(
SELECT COMPANY_CODE,COUNT(DISTINCT(EMPLOYEE_CODE)) AS ECNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE),
M AS(
SELECT COMPANY_CODE,COUNT(DISTINCT(MANAGER_CODE)) AS MCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE),
S AS(
SELECT COMPANY_CODE,COUNT(DISTINCT(SENIOR_MANAGER_CODE)) AS SCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE),
L AS(
SELECT COMPANY_CODE,COUNT(DISTINCT(LEAD_MANAGER_CODE)) AS LCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE)
SELECT C.COMPANY_CODE,C.FOUNDER,LCNT,SCNT,MCNT,ECNT FROM C
INNER JOIN L ON C.COMPANY_CODE = L.COMPANY_CODE
INNER JOIN S ON L.COMPANY_CODE = S.COMPANY_CODE
INNER JOIN M ON S.COMPANY_CODE = M.COMPANY_CODE
INNER JOIN E ON M.COMPANY_CODE = E.COMPANY_CODE
ORDER BY C.COMPANY_CODE;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
New Companies
You are viewing a single comment's thread. Return to all comments →
WITH C AS( SELECT COMPANY_CODE,FOUNDER FROM COMPANY ORDER BY COMPANY_CODE), E AS( SELECT COMPANY_CODE,COUNT(DISTINCT(EMPLOYEE_CODE)) AS ECNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE), M AS( SELECT COMPANY_CODE,COUNT(DISTINCT(MANAGER_CODE)) AS MCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE), S AS( SELECT COMPANY_CODE,COUNT(DISTINCT(SENIOR_MANAGER_CODE)) AS SCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE), L AS( SELECT COMPANY_CODE,COUNT(DISTINCT(LEAD_MANAGER_CODE)) AS LCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE) SELECT C.COMPANY_CODE,C.FOUNDER,LCNT,SCNT,MCNT,ECNT FROM C INNER JOIN L ON C.COMPANY_CODE = L.COMPANY_CODE INNER JOIN S ON L.COMPANY_CODE = S.COMPANY_CODE INNER JOIN M ON S.COMPANY_CODE = M.COMPANY_CODE INNER JOIN E ON M.COMPANY_CODE = E.COMPANY_CODE ORDER BY C.COMPANY_CODE;