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);