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.
SELECT
cp.company_code,
cp.founder,
aliased_table.distinct_lead_manager_count,
aliased_table.distinct_senior_manager_count,
aliased_table.distinct_manager_count,
aliased_table.distinct_employee_count
FROM company cp
INNER JOIN (
SELECT
c.company_code AS company_code,
COUNT(DISTINCT l.lead_manager_code) AS distinct_lead_manager_count,
COUNT(DISTINCT s.senior_manager_code) AS distinct_senior_manager_count,
COUNT(DISTINCT m.manager_code) AS distinct_manager_count,
COUNT(DISTINCT e.employee_code) AS distinct_employee_count
FROM
company c
INNER JOIN
lead_manager l ON l.company_code = c.company_code
INNER JOIN
senior_manager s ON l.lead_manager_code = s.lead_manager_code
INNER JOIN
manager m ON m.senior_manager_code = s.senior_manager_code
INNER JOIN
employee e ON e.manager_code = m.manager_code
GROUP BY
c.company_code
) AS aliased_table ON aliased_table.company_code = cp.company_code
ORDER BY cp.company_code ASC;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
SELECT cp.company_code, cp.founder, aliased_table.distinct_lead_manager_count, aliased_table.distinct_senior_manager_count, aliased_table.distinct_manager_count, aliased_table.distinct_employee_count FROM company cp INNER JOIN ( SELECT c.company_code AS company_code, COUNT(DISTINCT l.lead_manager_code) AS distinct_lead_manager_count, COUNT(DISTINCT s.senior_manager_code) AS distinct_senior_manager_count, COUNT(DISTINCT m.manager_code) AS distinct_manager_count, COUNT(DISTINCT e.employee_code) AS distinct_employee_count FROM company c INNER JOIN lead_manager l ON l.company_code = c.company_code INNER JOIN senior_manager s ON l.lead_manager_code = s.lead_manager_code INNER JOIN manager m ON m.senior_manager_code = s.senior_manager_code INNER JOIN employee e ON e.manager_code = m.manager_code GROUP BY c.company_code ) AS aliased_table ON aliased_table.company_code = cp.company_code ORDER BY cp.company_code ASC;