We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. New Companies
  5. Discussions

New Companies

  • jullienbeaufond1
     + 1 comment

    I had to use MS SQL Sever because CTE's are not available on the version of MySQL of Hackerrank.

    It can be done without CTE also, by joining tables only by derived tables. However, I see no use on joining on other tables when the Employee table already has almost all the information needed. 

    WITH cte_1 AS (
    SELECT 
        DISTINCT *
    FROM (
        SELECT 
            e.employee_code, 
            e.manager_code, 
            e.senior_manager_code, 
            e.lead_manager_code, 
            e.company_code, 
            c.founder
        FROM 
            Employee e
        JOIN 
            Company c
            ON 
                e.company_code = c.company_code) as table2)
SELECT 
    company_code, 
    founder, 
    COUNT(DISTINCT lead_manager_code),
    COUNT(DISTINCT senior_manager_code),
    COUNT(DISTINCT manager_code),
    COUNT(DISTINCT employee_code)
FROM 
    cte_1
GROUP BY 
    company_code, 
    founder
ORDER BY 
    company_code ASC;