[Hacker Rank] BASIC JOIN - 1

👻 QUESTION

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

 

🚀ANSWER

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

 

---

 

👻 QUESTION

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

 

🚀ANSWER

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

 

---

 

👻 QUESTION

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

 

🚀ANSWER

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

 

---

 

👻 QUESTION

You are given two tables: Students and Grades.
Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark.
Ketty doesn't want the NAMES of those students who received a grade lower than 8.
The report must be in descending order by grade -- i.e. higher grades are entered first.
If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically.
Finally, 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 (1-7) assigned to them, order those particular students by their marks in ascending order.
Write a query to help Eve.

 

🚀ANSWER

SELECT IF(G.GRADE>=8, S.NAME, NULL) AS NAME, G.GRADE, S.MARKS
FROM STUDENTS S
JOIN GRADES G
ON S.MARKS >= G.MIN_MARK AND S.MARKS <= G.MAX_MARK
ORDER BY G.GRADE DESC, S.NAME ASC;

 

---

 

👻 QUESTION

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard!
Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge.
Order your output in descending order by the total number of challenges in which the hacker earned a full score.
If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

 

🚀ANSWER

SELECT H.HACKER_ID, H.NAME
FROM HACKERS H
JOIN (
    SELECT S.HACKER_ID, COUNT(DISTINCT S.CHALLENGE_ID) AS SCORE
    FROM SUBMISSIONS S
    JOIN CHALLENGES C ON S.CHALLENGE_ID = C.CHALLENGE_ID
    JOIN DIFFICULTY D ON D.DIFFICULTY_LEVEL = C.DIFFICULTY_LEVEL
    WHERE S.SCORE = D.SCORE
    GROUP BY S.HACKER_ID
    HAVING COUNT(DISTINCT S.CHALLENGE_ID) > 1
) AS SC
ON H.HACKER_ID = SC.HACKER_ID
ORDER BY  SC.SCORE DESC, H.HACKER_ID ASC;