Refreshing SQL Skill

2018-09-07 13:12:00+00:00

Efficient SQL execution is very crucial for web app scalability. I felt my SQL skill was ~~very~~ a bit rusty.

I tried to find places to practices non trivial SQL and found a great one: https://www.hackerrank.com/domains/sql.

I managed to finished all the 58 problems and here is the result: https://www.hackerrank.com/Aldian

I am documenting some of my solutions here, just in case I want to read it again while I am offline.

Thank you hackerrank for creating such great content!

Hackerrank page: 15 Days of Learning SQL

Oracle solution:

SELECT hdr.submission_date, hdr.num_hackers, h.hacker_id, h.name  -- join with Hackers to get the hacker's name
FROM (
    SELECT hdr.submission_date, hdr.num_hackers, MIN(hdr.hacker_id) AS hacker_id  -- get the lowest hacker ID that has the maximum number of submissions on the date
    FROM (
        SELECT hdr.submission_date, hdr.num_hackers, s.hacker_id  -- get hacker IDs of all hackers managed to make the most submissions of the date
        FROM (
            SELECT  -- get the number of submissions of the hacker making the most submissions on each date
                hdr.submission_date, hdr.num_hackers, MAX(hdr.num_submissions) AS max_submissions
            FROM (
                SELECT  -- map hacker ID with the number of submissions he/she made on each date
                    hdr.submission_date, hdr.num_hackers, COUNT(s.hacker_id) AS num_submissions
                FROM (
                    SELECT s.submission_date, COUNT(DISTINCT s.hacker_id) AS num_hackers  -- map each date with the number of hackers that made at least one submission on that date.
                    FROM Submissions s
                    WHERE (  -- only include hackers that submit every day
                        SELECT COUNT(DISTINCT submission_date) FROM Submissions
                        WHERE hacker_id = s.hacker_id AND submission_date <= s.submission_date
                    ) = (
                        SELECT COUNT(DISTINCT submission_date) FROM Submissions
                        WHERE submission_date <= s.submission_date
                    )
                    GROUP BY s.submission_date
                ) hdr
                INNER JOIN Submissions s ON s.submission_date = hdr.submission_date
                GROUP BY hdr.submission_date, hdr.num_hackers, s.hacker_id
            ) hdr
            GROUP BY hdr.submission_date, hdr.num_hackers
        ) hdr
        INNER JOIN Submissions s ON s.submission_date = hdr.submission_date
        GROUP BY hdr.submission_date, hdr.num_hackers, s.hacker_id
        HAVING MIN(hdr.max_submissions) = COUNT(s.hacker_id)
    ) hdr
    GROUP BY hdr.submission_date, hdr.num_hackers
) hdr
INNER JOIN Hackers h ON h.hacker_id = hdr.hacker_id
ORDER BY hdr.submission_date;

MySQL solution:

SELECT hdr.submission_date, hdr.num_hackers, h.hacker_id, h.name
FROM (
    SELECT hdr.submission_date, hdr.num_hackers, MIN(hdr.hacker_id) AS hacker_id
    FROM (
        SELECT hdr.submission_date, hdr.num_hackers, s.hacker_id
        FROM (
            SELECT
                hdr.submission_date, hdr.num_hackers, MAX(hdr.num_submissions) AS max_submissions
            FROM (
                SELECT
                    hdr.submission_date, hdr.num_hackers, COUNT(s.hacker_id) AS num_submissions
                FROM (
                    SELECT s.submission_date, COUNT(DISTINCT s.hacker_id) AS num_hackers
                    FROM Submissions s
                    WHERE (
                        SELECT COUNT(DISTINCT submission_date) FROM Submissions
                        WHERE hacker_id = s.hacker_id AND submission_date <= s.submission_date
                    ) = (
                        SELECT COUNT(DISTINCT submission_date) FROM Submissions
                        WHERE submission_date <= s.submission_date
                    )
                    GROUP BY s.submission_date
                ) hdr
                INNER JOIN Submissions s ON s.submission_date = hdr.submission_date
                GROUP BY hdr.submission_date, hdr.num_hackers, s.hacker_id
            ) hdr
            GROUP BY hdr.submission_date, hdr.num_hackers
        ) hdr
        INNER JOIN Submissions s ON s.submission_date = hdr.submission_date
        GROUP BY hdr.submission_date, hdr.num_hackers, s.hacker_id
        HAVING MIN(hdr.max_submissions) = COUNT(s.hacker_id)
    ) hdr
    GROUP BY hdr.submission_date, hdr.num_hackers
) hdr
INNER JOIN Hackers h ON h.hacker_id = hdr.hacker_id
ORDER BY hdr.submission_date;

Hackerrank page: Interviews

MS SQL solution:

SELECT
    ch.contest_id, ch.hacker_id, ch.name,
    SUM(ch.total_submissions),
    SUM(ch.total_accepted_submissions),
    SUM(ch.total_views),
    SUM(ch.total_unique_views)
FROM (
    SELECT
        ch.contest_id, ch.hacker_id, ch.name, ch.challenge_id,
        MIN(ch.total_submissions) AS total_submissions,
        MIN(ch.total_accepted_submissions) AS total_accepted_submissions,
        SUM(v.total_views) AS total_views,
        SUM(v.total_unique_views) AS total_unique_views
    FROM (
        SELECT
            c.contest_id, c.hacker_id, c.name, ch.challenge_id,
            SUM(s.total_submissions) AS total_submissions,
            SUM(s.total_accepted_submissions) AS total_accepted_submissions
        FROM Contests c
        INNER JOIN Colleges cg ON cg.contest_id = c.contest_id
        INNER JOIN Challenges ch ON ch.college_id = cg.college_id
        LEFT JOIN Submission_Stats s ON s.challenge_id = ch.challenge_id
        GROUP BY c.contest_id, c.hacker_id, c.name, ch.challenge_id
    ) ch
    LEFT JOIN View_Stats v ON v.challenge_id = ch.challenge_id
    GROUP BY ch.contest_id, ch.hacker_id, ch.name, ch.challenge_id
) ch
GROUP BY ch.contest_id, ch.hacker_id, ch.name
HAVING
    SUM(ch.total_submissions) > 0
    OR SUM(ch.total_accepted_submissions) > 0
    OR SUM(ch.total_views) > 0
    OR SUM(ch.total_unique_views) > 0
ORDER BY ch.contest_id;

Oracle solution:

SELECT
    ch.contest_id, ch.hacker_id, ch.name,
    COALESCE(SUM(ch.total_submissions), 0) AS total_submissions,
    COALESCE(SUM(ch.total_accepted_submissions), 0) AS total_accepted_submissions,
    COALESCE(SUM(ch.total_views), 0) AS total_views,
    COALESCE(SUM(ch.total_unique_views), 0) AS total_unique_views
FROM (
    SELECT
        ch.contest_id, ch.hacker_id, ch.name, ch.challenge_id,
        MIN(ch.total_submissions) AS total_submissions,
        MIN(ch.total_accepted_submissions) AS total_accepted_submissions,
        SUM(v.total_views) AS total_views,
        SUM(v.total_unique_views) AS total_unique_views
    FROM (
        SELECT
            c.contest_id, c.hacker_id, c.name, ch.challenge_id,
            SUM(s.total_submissions) AS total_submissions,
            SUM(s.total_accepted_submissions) AS total_accepted_submissions
        FROM Contests c
        INNER JOIN Colleges cg ON cg.contest_id = c.contest_id
        INNER JOIN Challenges ch ON ch.college_id = cg.college_id
        LEFT JOIN Submission_Stats s ON s.challenge_id = ch.challenge_id
        GROUP BY c.contest_id, c.hacker_id, c.name, ch.challenge_id
    ) ch
    LEFT JOIN View_Stats v ON v.challenge_id = ch.challenge_id
    GROUP BY ch.contest_id, ch.hacker_id, ch.name, ch.challenge_id
) ch
GROUP BY ch.contest_id, ch.hacker_id, ch.name
HAVING
    SUM(ch.total_submissions) > 0
    OR SUM(ch.total_accepted_submissions) > 0
    OR SUM(ch.total_views) > 0
    OR SUM(ch.total_unique_views) > 0
ORDER BY ch.contest_id;

MySQL solution:

SELECT
    ch.contest_id, ch.hacker_id, ch.name,
    SUM(ch.total_submissions),
    SUM(ch.total_accepted_submissions),
    SUM(ch.total_views),
    SUM(ch.total_unique_views)
FROM (
    SELECT
        ch.contest_id, ch.hacker_id, ch.name, ch.challenge_id,
        MIN(ch.total_submissions) AS total_submissions,
        MIN(ch.total_accepted_submissions) AS total_accepted_submissions,
        SUM(v.total_views) AS total_views,
        SUM(v.total_unique_views) AS total_unique_views
    FROM (
        SELECT
            c.contest_id, c.hacker_id, c.name, ch.challenge_id,
            SUM(s.total_submissions) AS total_submissions,
            SUM(s.total_accepted_submissions) AS total_accepted_submissions
        FROM Contests c
        INNER JOIN Colleges cg ON cg.contest_id = c.contest_id
        INNER JOIN Challenges ch ON ch.college_id = cg.college_id
        LEFT JOIN Submission_Stats s ON s.challenge_id = ch.challenge_id
        GROUP BY c.contest_id, c.hacker_id, c.name, ch.challenge_id
        ORDER BY c.contest_id
    ) ch
    LEFT JOIN View_Stats v ON v.challenge_id = ch.challenge_id
    GROUP BY ch.contest_id, ch.hacker_id, ch.name, ch.challenge_id
) ch
GROUP BY ch.contest_id, ch.hacker_id, ch.name
HAVING
    SUM(ch.total_submissions) > 0
    OR SUM(ch.total_accepted_submissions) > 0
    OR SUM(ch.total_views) > 0
    OR SUM(ch.total_unique_views) > 0

Hackerrank page: The PADS

MySQL Solution:

SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')')
FROM OCCUPATIONS
ORDER BY Name, Occupation;

SELECT CONCAT('There are a total of ', stat.cnt, ' ', LOWER(stat.occupation), 's.')
FROM (
    SELECT occupation, COUNT(*) AS cnt
    FROM OCCUPATIONS
    GROUP BY Occupation
) stat
ORDER BY stat.cnt, stat.occupation;

Hackerrank page: Occupations

Oracle Solution:

select *
from (select min(doctor) d, min(professor) p, min(singer) s, min(actor) a
      from (SELECT CASE WHEN Occupation = 'Doctor' THEN name END       AS Doctor,
                   CASE WHEN Occupation = 'Professor' THEN name END    AS Professor,
                   CASE WHEN Occupation = 'Singer' THEN name END       AS Singer,
                   CASE WHEN Occupation = 'Actor' THEN name END        AS Actor,
                   RANK() OVER (PARTITION BY Occupation order by name) AS row_rank
            FROM Occupations)x
      group by row_rank)
order by d, p, s, a;

MySQL Solution:

set @r1=0, @r2=0, @r3=0, @r4=0;
select max(Doctor), max(Professor), max(Singer), max(Actor)
from(
  select case when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
    case when Occupation='Doctor' then Name end as Doctor,
    case when Occupation='Professor' then Name end as Professor,
    case when Occupation='Singer' then Name end as Singer,
    case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name
) Temp
group by RowNumber

Hackerrank page: Binary Tree Nodes

MySQL Solution:

SELECT
    N,
    CASE
        WHEN P IS NULL THEN 'Root'
        WHEN N NOT IN (SELECT DISTINCT b.P FROM BST b WHERE b.P IS NOT NULL) THEN 'Leaf'
        ELSE 'Inner'
    END
FROM BST
ORDER BY N;

Hackerrank page: New Companies

MySQL Solution:

SELECT c.company_code, c.founder, COUNT(DISTINCT lm.lead_manager_code), COUNT(DISTINCT sm.senior_manager_code), COUNT(DISTINCT m.manager_code), COUNT(DISTINCT e.employee_code)
FROM Company c
LEFT JOIN Lead_Manager lm ON lm.company_code = c.company_code
LEFT JOIN Senior_Manager sm ON sm.company_code = c.company_code
LEFT JOIN Manager m ON m.company_code = c.company_code
LEFT JOIN Employee e ON e.company_code = c.company_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code;

Hackerrank page: Weather Observation Station 18

MySQL Solution:

SELECT ROUND(ABS(MAX(LAT_N) - MIN(LAT_N)) + ABS(MAX(LONG_W) - MIN(LONG_W)), 4)
FROM STATION;

Hackerrank page: Weather Observation Station 19

MySQL Solution:

SELECT ROUND(SQRT(POWER(MAX(LAT_N) - MIN(LAT_N), 2) + POWER(MAX(LONG_W) - MIN(LONG_W), 2)), 4)
FROM STATION;

Hackerrank page: Weather Observation Station 20

MySQL Solution:

SET @rownum = -1;
SELECT ROUND(AVG(LAT_N), 4)
FROM (
    SELECT @rownum := @rownum + 1 AS row_num, LAT_N
    FROM STATION
    ORDER BY LAT_N
) st
WHERE row_num IN (FLOOR(@rownum / 2), CEIL(@rownum / 2))

Hackerrank page: The Report

MySQL Solution:

SELECT CASE WHEN st.Marks >= 70 THEN st.Name END, (SELECT gr.Grade FROM Grades gr WHERE gr.Min_Mark <= st.Marks AND gr.Max_Mark >= st.Marks) AS Grade, st.Marks
FROM Students st
ORDER BY Grade DESC, st.Name

Hackerrank page: Top Competitors

MySQL Solution:

SELECT h.hacker_id, h.name
FROM Hackers h
INNER JOIN Submissions s ON s.hacker_id = h.hacker_id
INNER JOIN Challenges c ON c.challenge_id = s.challenge_id
INNER JOIN Difficulty d ON d.difficulty_level = c.difficulty_level
WHERE s.score = d.score
GROUP BY h.hacker_id, h.name
HAVING COUNT(DISTINCT s.submission_id) > 1
ORDER BY COUNT(DISTINCT s.submission_id) DESC, h.hacker_id;

Hackerrank page: Ollivander's Inventory

Oracle Solution:

SELECT w.id, fw.age, w.coins_needed, w.power
FROM Wands w
INNER JOIN Wands_Property wt ON w.code = wt.code
INNER JOIN (
    SELECT wt.age, MIN(w.coins_needed) AS price, w.power
    FROM Wands w
    INNER JOIN Wands_Property wt ON wt.code = w.code
    WHERE wt.is_evil = 0
    GROUP BY wt.age, w.power
    ORDER BY w.power DESC, wt.age DESC
) fw ON fw.price = w.coins_needed AND fw.power = w.power AND wt.age = fw.age
ORDER BY w.power DESC, fw.age DESC;

MySQL Solution:

SELECT w.id, fw.age, w.coins_needed, w.power
FROM Wands w
INNER JOIN Wands_Property wt ON w.code = wt.code
INNER JOIN (
    SELECT wt.age, MIN(w.coins_needed) AS price, w.power
    FROM Wands w
    INNER JOIN Wands_Property wt ON wt.code = w.code
    WHERE wt.is_evil = 0
    GROUP BY wt.age, w.power
) fw ON fw.price = w.coins_needed AND fw.power = w.power AND wt.age = fw.age
ORDER BY w.power DESC, fw.age DESC;

Hackerrank page: Challenges

MySQL Solution:

SELECT h.hacker_id, h.name, COUNT(c.challenge_id) as c_count
FROM Hackers as h
INNER JOIN Challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING
    c_count = (
        SELECT MAX(c_counts.num_c)
        FROM (
            SELECT COUNT(challenge_id) AS num_c
            FROM Challenges
            GROUP BY hacker_id
        ) c_counts
    )
    OR c_count IN (
        SELECT c_counts.num_c
        FROM (
            SELECT COUNT(challenge_id) AS num_c
            FROM challenges
            GROUP BY hacker_id
        ) c_counts
        GROUP BY c_counts.num_c
        HAVING COUNT(c_counts.num_c) = 1
    )

ORDER BY c_count DESC, c.hacker_id;

Hackerrank page: Contest Leaderboard

MySQL Solution:

SELECT hacker_id, name, SUM(max_score) AS total_score
FROM (
    SELECT h.hacker_id, h.name, s.challenge_id, MAX(s.score) AS max_score
    FROM Hackers h
    INNER JOIN Submissions s ON s.hacker_id = h.hacker_id
    GROUP BY h.hacker_id, h.name, s.challenge_id
) Max_Submission
GROUP BY hacker_id, name
HAVING total_score > 0
ORDER BY total_score DESC, hacker_id

Hackerrank page: Projects

MySQL Solution:

SELECT Start_Date, MIN(End_Date)
FROM
    (
        SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)
    ) Prj_Start_Dates,
    (
        SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)
    ) Prj_End_Dates
WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY DATEDIFF(MIN(End_Date), Start_Date), Start_Date;

Hackerrank page: Placements

MySQL Solution:

SELECT s.Name
FROM Students s
INNER JOIN Friends f ON s.ID = f.ID
INNER JOIN Packages p1 ON p1.ID = s.ID
INNER JOIN Packages p2 ON p2.ID = f.Friend_ID
WHERE p2.Salary - p1.Salary > 0
ORDER BY p2.Salary

Hackerrank page: Symmetric Pairs

MySQL Solution:

SET @id1 = 0, @id2 = 0;
SELECT DISTINCT f1.X, f2.X
FROM
    (SELECT @id1 := @id1 + 1 AS ID, X, Y FROM Functions) f1,
    (SELECT @id2 := @id2 + 1 AS ID, X, Y FROM Functions) f2
WHERE f1.ID <> f2.ID AND f1.X = f2.Y AND f1.Y = f2.X AND f1.X <= f2.X
ORDER BY f1.X, f2.X

Hackerrank page: Print Prime Numbers

MySQL Solution:

SELECT GROUP_CONCAT(OriginalNumber SEPARATOR '&')
FROM (
    SELECT 0 AS ID, OriginalNumber
    FROM (
        SELECT Sequence.x AS OriginalNumber
        FROM (
            SELECT  NumberGenerator.x
            FROM (
                SELECT (h*100+t*10+u+1) AS x
                FROM (
                    SELECT 0 AS h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                ) A, (
                    SELECT 0 AS t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                ) B, (
                    SELECT 0 AS u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                ) C
            ) AS NumberGenerator
            WHERE x > 1
            ORDER BY x
        ) Sequence, (
            SELECT (h*100+t*10+u+1) AS x
            FROM (
                SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
            ) A, (
                SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
            ) B, (
                SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
            ) C
        ) NumberGenerator2
        WHERE NumberGenerator2.x <= FLOOR(SQRT(Sequence.x)) AND Sequence.x % NumberGenerator2.x = 0
        GROUP BY Sequence.x
        HAVING COUNT(Sequence.x) = 1
    ) FinalResults
) Grouped
GROUP BY ID;