1. Reverse the order of all tables in join and see if results is coing, different or giving error ?
2. Use only company and employee table and see wheter you can solve it ?
3. Solve using multiple CTES

SELECT c.company_code, c.founder, (SELECT COUNT(DISTINCT lead_manager_code) FROM lead_manager AS l WHERE l.company_code = c.company_code ), (SELECT COUNT(DISTINCT senior_manager_code) FROM senior_manager AS s WHERE s.company_code = c.company_code ), (SELECT COUNT(DISTINCT manager_code) FROM manager AS m WHERE m.company_code = c.company_code ), (SELECT COUNT(DISTINCT employee_code) FROM employee AS e WHERE e.company_code = c.company_code ) FROM company AS c ORDER BY c.company_code ASC;

select c.company_code, c.founder, count(distinct(e.lead_manager_code)) as total_lead_managers, count(distinct(e.senior_manager_code)) as total_senior_managers, count(distinct(e.manager_code)) as total_managers, count(distinct(e.employee_code)) as total_employees from company c join employee e on e.company_code = c.company_code group by c.company_code, c.founder order by c.company_code

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;