Managing Corporate Hierarchies with SQL: A Case Study

The Task

Note:

• The tables may contain duplicate records.
• The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

Input Format

The following tables contain company data:

Company: The company_code is the code of the company and founder is the founder of the company. 

Lead_Manager: The lead_manager_code is the code of the lead manager, and the company_code is the code of the working company.

Senior_Manager: The senior_manager_code is the code of the senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company. 

Manager: The manager_code is the code of the manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.

Employee: The employee_code is the code of the employee, the manager_code is the code of its manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.

Sample Input

Company Table: 

 Lead_Manager Table:

Senior_Manager Table:

Manager Table:

 Employee Table: 

Sample Output

C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2

Explanation

In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1. There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under manager M1.

In company C2, the only lead manager is LM2. There is one senior manager, SM3, under LM2. There are two managers, M2 and M3, under senior manager SM3. There is one employee, E3, under manager M2, and another employee, E4, under manager, M3.

Let’s solve this problem with MySQL

To achieve the desired output, we will need to use a combination of JOIN operations and aggregation functions. We will join the various tables based on the company code and then count the number of lead managers, senior managers, managers, and employees for each company. The query will be ordered by the company_code.

Here’s the SQL query to accomplish this:

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;

Explanation:

1. Select Clause:
   • c.company_code: The code of the company.
   • c.founder: The founder of the company.
   • COUNT(DISTINCT lm.lead_manager_code) AS total_lead_managers: Counts the number of unique lead managers for each company.
   • COUNT(DISTINCT sm.senior_manager_code) AS total_senior_managers: Counts the number of unique senior managers for each company.
   • COUNT(DISTINCT m.manager_code) AS total_managers: Counts the number of unique managers for each company.
   • COUNT(DISTINCT e.employee_code) AS total_employees: Counts the number of unique employees for each company.
2. Join Operations:
   • LEFT JOIN Lead_Manager lm ON c.company_code = lm.company_code: Joins the Company table with the Lead_Manager table.
   • LEFT JOIN Senior_Manager sm ON c.company_code = sm.company_code: Joins the Company table with the Senior_Manager table.
   • LEFT JOIN Manager m ON c.company_code = m.company_code: Joins the Company table with the Manager table.
   • LEFT JOIN Employee e ON c.company_code = e.company_code: Joins the Company table with the Employee table.
3. Group By Clause:
   • GROUP BY c.company_code, c.founder: Groups the results by company code and founder.
4. Order By Clause:
   • ORDER BY c.company_code: Orders the output by the company code in ascending order.

The query will produce the following output:

This matches the expected sample output.