New Companies Hackerrank solution (two solutions)

New Companies.sql

SELECT company.company_code,company.founder,
count(distinct Lead_manager.lead_manager_code),
count(distinct Senior_manager.senior_manager_code),
count(distinct Manager.manager_code),count(distinct Employee.employee_code)
from company
INNER JOIN Lead_Manager
on company.company_code = Lead_Manager.company_code

INNER JOIN Senior_Manager
on company.company_code = Senior_Manager.company_code

INNER JOIN Manager
on company.company_code = Manager.company_code

INNER JOIN Employee
on company.company_code = Employee.company_code
GROUP BY company.company_code,company.founder
order by company.company_code asc


-- -------------------------------- ANOTHER SOLUTION ( BETTER )

select E.company_code, C.founder,
count(distinct lead_manager_code), count(distinct senior_manager_code), 
count(distinct manager_code), count(distinct employee_code) 
from Employee E inner join Company C on E.company_code = C.company_code group by E.company_code, C.founder order by E.company_code