SQL Interview Question — HackerRank — NEW COMPANIES | Advanced Select

Amber’s conglomerate has recently acquired several new companies, each with a unique hierarchical structure consisting of lead managers, senior managers, managers, and employees. To efficiently manage these companies, it’s crucial to understand the hierarchy and obtain specific details about each company. This blog will walk you through how to write an SQL query to retrieve the desired information and provide a structured overview of each company.

Problem Statement

We have a set of tables representing the hierarchical structure of various companies. The task is to write an SQL query that prints the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. The output should be ordered by the company_code in ascending order.

Problem Statement Link-

https://www.hackerrank.com/challenges/the-company/problem

Table Schemas

Company Table

• company_code: Code of the company (string).
• founder: Name of the founder.

Lead_Manager Table

• lead_manager_code: Code of the lead manager.
• company_code: Code of the company.

Senior_Manager Table

• senior_manager_code: Code of the senior manager.
• lead_manager_code: Code of the lead manager.
• company_code: Code of the company.

Manager Table

• manager_code: Code of the manager.
• senior_manager_code: Code of the senior manager.
• lead_manager_code: Code of the lead manager.
• company_code: Code of the company.

Employee Table

• employee_code: Code of the employee.
• manager_code: Code of the manager.
• senior_manager_code: Code of the senior manager.
• lead_manager_code: Code of the lead manager.
• company_code: Code of the company.

Approach

To solve this problem, we need to perform the following steps:

1. Count the number of unique lead managers in each company.
2. Count the number of unique senior managers in each company.
3. Count the number of unique managers in each company.
4. Count the number of unique employees in each company.
5. Join the counts with the company table to get the founder name and company_code.
6. Order the results by company_code in ascending order.

Step-by-Step Solution

We need to write a SQL query to achieve the desired output. Here’s a breakdown of the steps involved:

1. Join Tables

We need to join the five tables (Company, Lead_Manager, Senior_Manager, Manager, and Employee) based on the company hierarchy.

Join Company with each of the other tables using the company_code column. This will connect company information with its employees and management structure.

2. Count Employees

Use COUNT(DISTINCT employee_code) in the Employee table to get the total number of distinct employees for each company.

3. Count Managers

Similar to employees, use COUNT(DISTINCT manager_code) in the Manager table to get the total distinct managers for each company.

4. Count Senior Managers

Use COUNT(DISTINCT senior_manager_code) in the Senior_Manager table to get the total distinct senior managers for each company.

5. Count Lead Managers

Use COUNT(DISTINCT lead_manager_code) in the Lead_Manager table to get the total distinct lead managers for each company.

6. Select and Order

• Select the desired columns: company_code, founder, number of lead managers, number of senior managers, number of managers, and number of employees.
• Use aliases for the count functions for better readability (e.g., num_lm for number of lead managers).
• Order the results by company_code in ascending order.

Check out my video for a detailed solution explanation of the above problem statement —

Solution Query Code —

SELECT 
    c.company_code, 
    c.founder, 
    COUNT(DISTINCT l.lead_manager_code), 
    COUNT(DISTINCT s.senior_manager_code), 
    COUNT(DISTINCT m.manager_code), 
    COUNT(DISTINCT e.employee_code)
FROM Company c
JOIN Lead_Manager l
ON c.company_code = l.company_code
JOIN Senior_Manager s
ON l.company_code = s.company_code
JOIN Manager m
ON s.company_code = m.company_code
JOIN Employee e
ON m.company_code = e.company_code
GROUP BY c.company_code, c.founder
ORDER BY company_code