HackerRank: [SQL Advanced Select] (5/5) NEW COMPANIES | left join, count(distinct col) in SQL

I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

Click Here for the previous blog-post in the series.

Recommended SQL Courses:

1. Udemy: SQL - MySQL for Data Analytics and Business Intelligence
2. LinkedIn: Master SQL for Data Science
3. edX: Databases: Advanced Topics in SQL
4. edureka: SQL Essentials Training & Certification
5. Coursera: Learn SQL Basics for Data Science
6. Eduonix: SQL Queries

---

SQL Problem Statement:

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

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.

---

Solution-1: Using LEFT JOIN & COUNT(DISTINCT) (MySQL Query):

SELECT
  c.company_code,
  c.founder,
  count(distinct l.lead_manager_code) as no_of_lead_manager,
  count(distinct s.senior_manager_code) as no_of_senior_manager,
  count(distinct m.manager_code) as no_of_manager,
  count(distinct e.employee_code) as no_of_employee
FROM
  Company c
  LEFT JOIN Lead_Manager l ON c.company_code = l.company_code
  LEFT JOIN Senior_Manager s ON c.company_code = s.company_code
  AND l.lead_manager_code = s.lead_manager_code
  LEFT JOIN Manager m ON c.company_code = m.company_code
  AND l.lead_manager_code = m.lead_manager_code
  AND s.senior_manager_code = m.senior_manager_code
  LEFT JOIN Employee e ON c.company_code = e.company_code
  AND l.lead_manager_code = e.lead_manager_code
  AND s.senior_manager_code = e.senior_manager_code
  AND m.manager_code = e.manager_code
GROUP BY
  c.company_code,
  founder
ORDER BY
  c.company_code asc;

NOTE: 

1. LEFT JOIN is used to join 2 tables where all records from the left table are retrieved with matching records of the right table.
2. COUNT(DISTINCT col) is used to calculate the count of unique values. (To get rid of duplicate counts) 

---

Sample Output:

C1 Angela 1 2 5 13
C10 Earl 1 1 2 3
C100 Aaron 1 2 4 10
C11 Robert 1 1 1 1
C12 Amy 1 2 6 14
C13 Pamela 1 2 5 14
C14 Maria 1 1 3 5
C15 Joe 1 1 2 3
C16 Linda 1 1 3 5
C17 Melissa 1 2 3 7
C18 Carol 1 2 5 6
C19 Paula 1 2 4 7
C2 Frank 1 1 1 3

--------------------------------------------------------------------------------

Click here to see solutions for all Machine Learning Coursera Assignments.

&

Click here to see more codes for Raspberry Pi 3 and similar Family.

&

Click here to see more codes for NodeMCU ESP8266 and similar Family.

&

Click here to see more codes for Arduino Mega (ATMega 2560) and similar Family.

Feel free to ask doubts in the comment section. I will try my best to answer it.

If you find this helpful by any mean like, comment and share the post.

This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,

-Akshay P Daga