New Companies

JOIN, GROUP BY, COUNT, ORDER BY

Question Link

Each of the companies follows this hierarchy:

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

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

Solution:

SELECT
    e.company_code,
    c.founder,
    COUNT(DISTINCT e.lead_manager_code) AS ld_mngr_cnt,
    COUNT(DISTINCT e.senior_manager_code) AS snr_mngr_cnt,
    COUNT(DISTINCT e.manager_code) AS mngr_cnt,
    COUNT(DISTINCT e.employee_code) AS emp_cnt
FROM employee e
JOIN company c 
ON e.company_code = c.company_code
GROUP BY e.company_code, c.founder
ORDER BY e.company_code

Link to Full Output