HackerRank SQL - Basic Join (MySQL)

 

Solve SQL Code Challenges

 

African Cities

SELECT CITY.NAME FROM CITY 
				 JOIN COUNTRY 
                 ON CITY.COUNTRYCODE = COUNTRY.CODE 
WHERE COUNTRY.CONTINENT = 'Africa';

 

 

Average Population of Each Continent

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY
                            JOIN COUNTRY
                            ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT;

 

 

The Report

SELECT 
    CASE WHEN G.Grade > 7 THEN S.Name ELSE NULL END AS Name,
    G.Grade, 
    S.Marks 
FROM Students S JOIN Grades G
ON S.Marks >= G.Min_Mark AND S.Marks <= Max_Mark 
ORDER BY 
    G.Grade DESC,
    (CASE WHEN S.Name IS NOT NULL THEN S.Name ELSE S.Marks END) ASC;

 

 

Top Competitors

SELECT H.hacker_id, H.name FROM Submissions S
                  JOIN Hackers H ON S.hacker_id = H.hacker_id
                  JOIN Challenges C ON S.challenge_id = C.challenge_id
                  JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
WHERE S.score = D.score AND D.difficulty_level = C.difficulty_level
GROUP BY H.hacker_id, H.name
HAVING COUNT(H.hacker_id) > 1
ORDER BY COUNT(H.hacker_id) DESC, H.hacker_id;

 

 

Ollivander's Inventory

SELECT W.id, WP.age, W.coins_needed, W.power FROM Wands_Property WP
JOIN Wands W ON WP.code = W.code
WHERE WP.is_evil = 0
AND W.coins_needed = (SELECT MIN(W1.coins_needed) FROM Wands W1 JOIN Wands_Property WP1
                     ON W1.code = WP1.code WHERE WP1.is_evil = 0
                     AND W1.power = W.power
                     AND WP1.age = WP.age)
ORDER BY W.power DESC, WP.age DESC;

 

 

Challenges

SELECT H.hacker_id, H.name, COUNT(*) AS challenges_created FROM Hackers H JOIN Challenges C
ON H.hacker_id = C.hacker_id
GROUP BY H.hacker_id, H.name
HAVING challenges_created = 
(
    SELECT MAX(sub.challenge_created) FROM (
        SELECT COUNT(*) AS challenge_created FROM Hackers H JOIN Challenges C ON H.hacker_id = C.hacker_id
    GROUP BY H.hacker_id
    ) sub
)
OR
challenges_created IN 
(
    SELECT sub.challenge_created FROM (
        SELECT hacker_id, COUNT(*) AS challenge_created FROM Challenges GROUP BY Challenges.hacker_id) sub
    GROUP BY sub.challenge_created HAVING COUNT(*) = 1
)
ORDER BY challenges_created DESC, H.hacker_id;

 

 

Contest Leaderboard

SELECT MS.hacker_id, H.name, SUM(MS.score) AS totalScore FROM (
    SELECT hacker_id, challenge_id, MAX(score) AS score FROM Submissions
    GROUP BY hacker_id, challenge_id
) AS MS JOIN Hackers H ON MS.hacker_id = H.hacker_id
GROUP BY MS.hacker_id, H.name
HAVING totalScore > 0
ORDER BY totalScore DESC, MS.hacker_id;

 

 

Population Census

SELECT SUM(CITY.POPULATION) FROM CITY 
                            JOIN COUNTRY
                            ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Asia';