-- CREATE
CREATE TABLE Company
(
    company_code VARCHAR(255) PRIMARY KEY,
    founder      VARCHAR(255)
);


CREATE TABLE Lead_Manager
(
    lead_manager_code VARCHAR(255) PRIMARY KEY,
    company_code      VARCHAR(255)
);


CREATE TABLE Senior_Manager
(
    senior_manager_code VARCHAR(255) PRIMARY KEY,
    lead_manager_code   VARCHAR(255),
    company_code        VARCHAR(255)
);

CREATE TABLE Manager
(
    manager_code        VARCHAR(255) PRIMARY KEY,
    senior_manager_code VARCHAR(255),
    lead_manager_code   VARCHAR(255),
    company_code        VARCHAR(255)
);

CREATE TABLE Employee
(
    employee_code       VARCHAR(255) PRIMARY KEY,
    manager_code        VARCHAR(255),
    senior_manager_code VARCHAR(255),
    lead_manager_code   VARCHAR(255),
    company_code        VARCHAR(255)
);

-- INSERT

INSERT INTO Company (company_code, founder)
VALUES ('C001', 'Alice Johnson'),
       ('C002', 'Bob Smith');

INSERT INTO Lead_Manager (lead_manager_code, company_code)
VALUES ('LM001', 'C001'),
       ('LM002', 'C002');

INSERT INTO Senior_Manager (senior_manager_code, lead_manager_code, company_code)
VALUES ('SM001', 'LM001', 'C001'),
       ('SM002', 'LM002', 'C002');

INSERT INTO Manager (manager_code, senior_manager_code, lead_manager_code, company_code)
VALUES ('M001', 'SM001', 'LM001', 'C001'),
       ('M002', 'SM002', 'LM002', 'C002');

INSERT INTO Employee (employee_code, manager_code, senior_manager_code, lead_manager_code, company_code)
VALUES ('E001', 'M001', 'SM001', 'LM001', 'C001'),
       ('E002', 'M001', 'SM001', 'LM001', 'C001'),
       ('E003', 'M002', 'SM002', 'LM002', 'C002'),
       ('E004', 'M002', 'SM002', 'LM002', 'C002');


-- QUERY

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