Hacker Rank SQL

New Companies Solution

Hello Friends in this article i am gone to share Hacker Rank SQL Solutions with you | New Companies Solution


Also Visit:ย  Binary Tree Nodes Solution


 

Problem

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_code founder
C1 Monika
C2 Samantha

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 โ€“ New Companies Solution

MySQL Code
select c.company_code, c.founder, count(distinct lm.lead_manager_code), 
count(distinct sm.senior_manager_code), count(distinct m.manager_code), 
count(distinct e.employee_code) from Company c, Lead_Manager lm, 
Senior_Manager sm, Manager m, Employee e

where c.company_code = lm.company_code and 
lm.lead_manager_code = sm.lead_manager_code and 
sm.senior_manager_code = m.senior_manager_code and 
m.manager_code = e.manager_code group by c.company_code, 
c.founder order by c.company_code

 

 

Disclaimer: The above Problems are generated by Hacker Rank but the Solutions are Provided by NYANDER.COM. All Hacker Rank SQL Solutions Shared only for Educational and Learning Purpose.