From my point of view, this task is not entirely correct. First, the database structure is not normalized — we have duplicated data across different tables, as each role table stores the company_code directly.

Second, the task asks for the count of all roles related to a company, but it doesn't clarify whether those counts should be based on the reporting hierarchy (e.g., only counting managers who report to existing senior managers, etc.). In fact, based on the current schema, the counts should be based solely on the company_code, regardless of whether someone reports to another role or not.

That means the result should not depend on whether a company has a lead manager, senior manager, and so on. Therefore, I propose the solution where each role is counted independently by company, based only on company_code.

I also suggest updating the task description to make this requirement clearer for others.

SELECT 
    Company.company_code,
    Company.founder,
    (SELECT COUNT(DISTINCT lead_manager_code) FROM Lead_Manager WHERE company_code = Company.company_code) AS total_lead_managers,
    (SELECT COUNT(DISTINCT senior_manager_code) FROM Senior_Manager WHERE company_code = Company.company_code) AS total_senior_managers,
    (SELECT COUNT(DISTINCT manager_code) FROM Manager WHERE company_code = Company.company_code) AS total_managers,
    (SELECT COUNT(DISTINCT employee_code) FROM Employee WHERE company_code = Company.company_code) AS total_employees
FROM Company
ORDER BY CAST(SUBSTRING(Company.company_code, 2, 3) AS UNSIGNED);

SELECT Company.company_code, Company.founder, COUNT(DISTINCT(Employee.lead_manager_code)), COUNT(DISTINCT Employee.senior_manager_code), COUNT(DISTINCT Employee.manager_code), COUNT(DISTINCT Employee.employee_code) FROM Employee JOIN Company ON Company.company_code = Employee.company_code GROUP BY 1,2 ORDER BY 1;

select company.company_code, company.founder, count(distinct lead_manager.lead_manager_code), count(distinct senior_manager.senior_manager_code), count(distinct manager.manager_code), count(distinct employee.employee_code) from company join lead_manager on company.company_code=lead_manager.company_code join senior_manager on company.company_code=senior_manager.company_code join manager on company.company_code=manager.company_code join employee on company.company_code=employee.company_code group by company.company_code, company.founder order by company.company_code;

Here is one valid option

SELECT COMPANY.company_code, COMPANY.founder, COUNT(DISTINCT Lead_Manager.lead_manager_CODE), COUNT(DISTINCT Senior_Manager.senior_manager_code), COUNT(DISTINCT Manager.manager_code), COUNT(DISTINCT Employee.employee_code) FROM COMPANY JOIN Lead_Manager ON COMPANY.company_code = Lead_Manager.company_code JOIN Senior_Manager ON COMPANY.company_code = Senior_Manager.company_code JOIN Manager ON COMPANY.company_code = Manager.company_code JOIN Employee ON COMPANY.company_code = Employee.company_code GROUP BY COMPANY.company_code, COMPANY.founder

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;