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.
here's the query for SQL SERVER considering that null values are present in some tables.
WITH LeadManagerCounts AS (
SELECT company_code, COUNT(DISTINCT lead_manager_code) AS lead_manager_count
FROM Lead_Manager
GROUP BY company_code
),
SeniorManagerCounts AS (
SELECT company_code, COUNT(DISTINCT senior_manager_code) AS senior_manager_count
FROM Senior_Manager
GROUP BY company_code
),
ManagerCounts AS (
SELECT company_code, COUNT(DISTINCT manager_code) AS manager_count
FROM Manager
GROUP BY company_code
),
EmployeeCounts AS (
SELECT company_code, COUNT(DISTINCT employee_code) AS employee_count
FROM Employee
GROUP BY company_code
)
SELECT
c.company_code,
c.founder,
lc.lead_manager_count ,
sc.senior_manager_count,
mc.manager_count,
ec.employee_count
FROM
company c
LEFT JOIN
LeadManagerCounts lc ON c.company_code = lc.company_code
LEFT JOIN
SeniorManagerCounts sc ON c.company_code = sc.company_code
LEFT JOIN
ManagerCounts mc ON c.company_code = mc.company_code
LEFT JOIN
EmployeeCounts ec ON c.company_code = ec.company_code
ORDER BY
c.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
New Companies
You are viewing a single comment's thread. Return to all comments →
here's the query for SQL SERVER considering that null values are present in some tables. WITH LeadManagerCounts AS ( SELECT company_code, COUNT(DISTINCT lead_manager_code) AS lead_manager_count FROM Lead_Manager GROUP BY company_code ), SeniorManagerCounts AS ( SELECT company_code, COUNT(DISTINCT senior_manager_code) AS senior_manager_count FROM Senior_Manager GROUP BY company_code ), ManagerCounts AS ( SELECT company_code, COUNT(DISTINCT manager_code) AS manager_count FROM Manager GROUP BY company_code ), EmployeeCounts AS ( SELECT company_code, COUNT(DISTINCT employee_code) AS employee_count FROM Employee GROUP BY company_code ) SELECT c.company_code, c.founder, lc.lead_manager_count , sc.senior_manager_count, mc.manager_count, ec.employee_count FROM company c LEFT JOIN LeadManagerCounts lc ON c.company_code = lc.company_code LEFT JOIN SeniorManagerCounts sc ON c.company_code = sc.company_code LEFT JOIN ManagerCounts mc ON c.company_code = mc.company_code LEFT JOIN EmployeeCounts ec ON c.company_code = ec.company_code ORDER BY c.company_code ASC;