HackerRank - SQL - Advanced Select

hackerRank-SQL-advanced-select.sql

/* New Companies */
SELECT 
    c.company_code
    , c.founder 
    , COUNT(DISTINCT l.lead_manager_code)
    , COUNT(DISTINCT s.senior_manager_code)
    , COUNT(DISTINCT m.manager_code)
    , COUNT(DISTINCT e.employee_code)
FROM Company AS c
LEFT JOIN Lead_Manager AS l ON c.company_code = l.company_code
LEFT JOIN Senior_Manager AS s ON c.company_code = s.company_code
LEFT JOIN Manager AS m ON c.company_code = m.company_code
LEFT JOIN Employee AS e ON c.company_code = e.company_code
GROUP BY c.company_code, c.founder 
ORDER BY c.company_code

/* Binary Tree Nodes */
SELECT N, 
CASE
    WHEN P IS NULL THEN "Root"
    WHEN N IN (SELECT P FROM BST) THEN "Inner"
    ELSE "Leaf"
END AS output FROM BST ORDER BY N

/* Occupations */
SELECT 
    MAX(CASE WHEN Occupation = "Doctor" THEN Name END) AS d,
    MAX(CASE WHEN Occupation = "Professor" THEN Name END) AS p,
    MAX(CASE WHEN Occupation = "Singer" THEN Name END) AS s,
    MAX(CASE WHEN Occupation = "Actor" THEN Name END) AS a
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS row_num
    FROM OCCUPATIONS) AS T
GROUP BY row_num
ORDER BY row_num

/* The PADS */
SELECT CONCAT(Name, "(", LEFT(Occupation, 1), ")") FROM OCCUPATIONS ORDER BY Name;
SELECT 
    "There are a total of"
    , COUNT(Occupation) AS count_occ
    , CONCAT(LCASE(Occupation), "s.") 
FROM OCCUPATIONS 
GROUP BY Occupation 
ORDER BY count_occ, Occupation;

/* Type of Triangle */
SELECT 
CASE 
    WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
    WHEN A = B AND A = C THEN 'Equilateral'
    WHEN A = B OR A = C OR B = C THEN 'Isosceles'
    ELSE 'Scalene'
END AS output
FROM TRIANGLES;