HackerRank Medium SQL Problems

/* 
Challenge: "Contest Leaderboard"

- The total score of a hacker is the sum of their maximum scores for all of the challenges. 
- Write a query to print the hacker_id, name, and total score of the hackers ordered 
by the descending score. 
- If more than one hacker achieved the same total score, then sort the result 
by ascending hacker_id. 
- Exclude all hackers with a total score of 0 from your result.
*/ 

select 
    hackers.hacker_id,
    hackers.name,
    sum(best_submissions.score)
from 
    (select 
        hacker_id, 
        challenge_id,
        max(score) as score
     from submissions
     group by hacker_id, challenge_id) as best_submissions
join hackers
on best_submissions.hacker_id = hackers.hacker_id
group by hackers.hacker_id, hackers.name
having sum(best_submissions.score) > 0
order by sum(best_submissions.score) desc, hackers.hacker_id;

/* 
Challenge: "The PADS"

Generate the following two result sets: 
1. Query an alphabetically ordered list of all names in OCCUPATIONS immediately
followed by the first letter of each profession as a parenthetical. For example:
AnActorName(A), ADoctorName (D), AProfessorName(P), and ASingerName(S). 

2. Query the number of occurrences of each occupation in OCCUPATIONS. Sort 
the occurrences in ascending order, and output them in the following format: 
"There are a total of [occupation_count] [occupation]s.", where occupation_count
is the number of occurrences in an occpuation in OCCUPATIONS and occupation is the
lowercase occupation name. If more than one occupation has the same 
occupation_count, they should be ordered alphabetically. 

Note: There will be at least two entries in the table for each type of occupation. 
*/ 

-- Query 1
select 
    concat(o.name, '(', left(o.occupation, 1), ')') as name_occupation
from occupations as o
order by o.name;

-- Query 2
select 
    concat('There are a total of ', count.occupation_count, ' ', lower(count.occupation), 's.') as totals from (
select
    occupation,
    count(occupation) over (partition by occupation) as occupation_count
from occupations) as count
group by count.occupation
order by count.occupation_count asc, count.occupation;

/* 
Challenge: "SQL Project Planning"

- Write a query to output the start and end dates of projects listed by the 
number of days it took to complete the project in ascending order. 
- If there is more than one project that have the same number of completion days, 
then order by the start date of the project. 
*/ 

with project_group as (
    select
        row_number() over (
            order by start_date) + 
            datediff(current_date(), start_date) as task_bucket, 
        start_date,
        end_date
    from projects)

select 
    min(start_date) as start, 
    max(end_date) as end
from project_group
group by task_bucket
order by datediff(end, start), start;

/* 
Challenge: "Occupations"

- Pivot the occupation column in OCCUPATIONS so that each name is sorted
alphabetically and displayed underneath its corresponding occupation. 
- The output column headers should be doctor, professor, singer, and 
actor, respectively. 
*/ 

with titles as (
    select
        name, 
        occupation, 
        if(occupation = 'Doctor', name, null) as Doctor, 
        if(occupation = 'Professor', name, null) as Professor,
        if(occupation = 'Singer', name, null) as Singer,
        if(occupation = 'Actor', name, null) as Actor, 
        row_number() over (
            partition by occupation 
            order by name) as ordering
    from occupations)
    
select 
    max(Doctor) as Doctor, 
    max(Professor) as Professor, 
    max(Singer) as Singer, 
    max(Actor) as Actor
from titles
group by ordering;

/* 
Challenge: "Binary Tree Nodes"

Write a query to find the node type of binary tree ordered by
the value of the node. Output one of the following for each node: 

1. Root: If node is root node. 
2. Leaf: If node is leaf node.
3. Inner: If node is neither root nor leaf node. 
*/ 

select
    n, 
    case
        when p is null then 'Root'
        when n in (select p from bst) then 'Inner'
        else 'Leaf'
    end as type
from bst
order by n;

/* 
Challenge: "New Companies" 

- 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 copmany code. 
*/ 

select 
    c.company_code, 
    c.founder, 
    count(distinct l.lead_manager_code) as total_lead_managers,
    count(distinct s.senior_manager_code) as total_senior_managers, 
    count(distinct m.manager_code) as total_managers, 
    count(distinct e.employee_code) as total_employees
from company as c

left join lead_manager as l
on l.company_code = c.company_code

left join senior_manager as s
on s.company_code = c.company_code

left join manager as m
on m.company_code = c.company_code

left join employee as e
on e.company_code = c.company_code

group by 
    c.company_code, 
    c.founder

order by c.company_code; 

/* 
The Challenge: "The Report" 

- Generate a report containing three columns: Name, Grade, and Mark. 
- Exclude the names of students who received a grade lower than 8. 
- The report must be in descending order by grade. 
- If there is more than on estudent with the same grade assigned to them, 
order those students by their name alphabetically. 
- If the grade is lower than 8, use "null" as their name and list
them by their grades in descending order. 
- If there is more than one student with the same grade assigned to them, 
order those students by their marks in ascending order. 

select
    case
        when s.marks >= 70 then s.name
        else null
    end as name,
    case
        when s.marks between 0 and 9 then 1
        when s.marks between 10 and 19 then 2
        when s.marks between 20 and 29 then 3
        when s.marks between 30 and 39 then 4
        when s.marks between 40 and 49 then 5
        when s.marks between 50 and 59 then 6
        when s.marks between 60 and 69 then 7
        when s.marks between 70 and 79 then 8
        when s.marks between 80 and 89 then 9
        when s.marks between 90 and 100 then 10
        else null
    end as grade,
    s.marks as mark
from students as s

order by 
    case
        when grade = 10 then 1
        when grade = 9 then 2
        when grade = 8 then 3
        when grade = 7 then 4
        when grade = 6 then 5
        when grade = 5 then 6
        when grade = 4 then 7
        when grade = 3 then 8
        when grade = 2 then 9
        when grade = 1 then 10
    end,
    s.name, 
    mark desc;