isn't doing left join to the company and employee table the most optimal way to solve this problem? Since employee table contains all the relevant data needed.

SELECT CONCAT(c.company_code," ", c.founder," ",COUNT(DISTINCT e.lead_manager_code), " ",COUNT(DISTINCT e.senior_manager_code)," ",COUNT(DISTINCT e.manager_code)," ",COUNT(DISTINCT e.employee_code)) FROM Company c JOIN Employee e ON c.company_code = e.company_code GROUP BY c.company_code, founder

SELECT

    Company.company_code,
founder,
COUNT(DISTINCT Lead_Manager.lead_manager_code) AS LM,
COUNT(DISTINCT Senior_Manager.senior_manager_code) AS SM,
COUNT(DISTINCT Manager.manager_code) AS M,
COUNT(DISTINCT Employee.employee_code) AS E

FROM Company

LEFT JOIN Lead_Manager ON Company.company_code = Lead_Manager.company_code

LEFT JOIN Senior_Manager ON Lead_Manager.lead_manager_code = Senior_Manager.lead_manager_code

LEFT JOIN Manager ON Senior_Manager.senior_manager_code = Manager.senior_manager_code LEFT JOIN Employee ON Manager.manager_code = Employee.manager_code GROUP BY Company.company_code, founder ORDER BY Company.company_code ASC;

MYSQL code:

SELECT c.company_code , c.founder , count(distinct lead_manager_code) as lead_mgrs , count(distinct senior_manager_code) AS sr_mgrs , count(distinct manager_code) AS mgrs , count(distinct employee_code) AS employees from Company c left join Employee e on e.company_code = c.company_code group by c.company_code, founder ORDER BY c.company_code;

my submission for mysql

SELECT DISTINCT
    C.company_code, 
    C.founder,
    COUNT(DISTINCT E.lead_manager_code) AS total_leads, 
    COUNT(DISTINCT E.senior_manager_code) AS total_seniors,
    COUNT(DISTINCT E.manager_code) AS total_managers,
    COUNT(DISTINCT E.employee_code) AS total_employees
FROM Company AS C
LEFT JOIN Employee AS E ON E.company_code = C.company_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code ASC;