SELECT C.company_code, C.founder, COUNT(DISTINCT LM.lead_manager_code) AS total_lead_managers, COUNT(DISTINCT SM.senior_manager_code) AS total_senior_managers, COUNT(DISTINCT M.manager_code) AS total_managers, COUNT(DISTINCT E.employee_code) AS total_employees FROM Company C LEFT JOIN Lead_Manager LM ON C.company_code = LM.company_code LEFT JOIN Senior_Manager SM ON C.company_code = SM.company_code LEFT JOIN Manager M ON C.company_code = M.company_code LEFT JOIN Employee E ON C.company_code = E.company_code GROUP BY C.company_code, C.founder ORDER BY C.company_code;

Interesting use case. One of my clients recently went through a similar restructuring on their eCommerce platform especially for men bamboo sleepwear based products, and we had to run hierarchical queries like this to audit team roles across multiple branches. Queries like these really help visualize organizational depth and optimize internal operations.

Easy Readable Solution

WITH CTE AS (SELECT 
company_code,
COUNT(DISTINCT(lead_manager_code)) as lm,
COUNT(DISTINCT(senior_manager_code)) as sm,
COUNT(DISTINCT(manager_code)) as m,
COUNT(DISTINCT(employee_code)) as e
from Employee
group by company_code)
select
CTE.company_code,
founder,
lm,
sm,
m,
e
from CTE
JOIN Company ON CTE.company_code = Company.company_code;

Oracle SQL using With, Single Join & a Group by Clause

With CTE as ( Select C.Company_Code, C.FOunder, E.employee_code, E.manager_code, E.senior_manager_code, E.lead_manager_code From Company C Left Join Employee E On C.Company_code = E.Company_code ) Select Company_Code, Founder, Count(Distinct(Lead_Manager_Code)), Count(Distinct(senior_manager_code)), Count(Distinct(manager_code)), Count(Distinct(employee_code)) From CTE Group by Company_Code, Founder Order by Company_Code;

Is the test case wrong? it expects company_code to be ordered like: [C1,C10,C100,C11...C2,C20,C21], would it not be better to have it expect the company codes as [C1,C2,C3...C100]?

edit: nvm, i guess it specifies the ordering even though that seems a bit dysfunctional.