[HackerRank] Basic Join 문제 정리

▶ Average Population of Each Continent.
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. (가장 가까운 정수로 내림)

SELECT COUNTRY.Continent, FLOOR(AVG(CITY.POPULATION))
FROM CITY
INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.Code
GROUP BY COUNTRY.Continent

▶The Report

• Students 테이블은 학생 정보, Grades 테이블은 점수 별 등급과 최소 점수, 최대 점수
• 8등급 미만의 학생들 이름 출력 X
• 등급 별로 내림차순
• 같은 등급의 학생이 2명 이상이면 학생들의 이름을 알파벳 순으로 출력
• 이름이 null일 때는 점수 순으로 출력
• 마지막으로, 8 미만의 등급인 학생들은 그들의 이름을 null 처리 -- CASE WHEN THEN ELSE END 조건
• 출력할 항목은 이름 / 등급 / 점수 순으로

SELECT CASE WHEN G.GRADE <8 THEN NULL ELSE S.NAME END AS NAME, G.GRADE, S.MARKS
FROM STUDENTS S
    INNER JOIN GRADES G ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK
ORDER BY G.GRADE DESC, S.NAME, S.MARKS

ㄴ CASE WHEN - THEN - ELSE - END
ㄴ INNER JOIN은 BETWEEN도 된다

▶Top Competitors
1) FULL SCORE(만점) 받은 챌린지가 2개 이상인 참가자들을 찾고 -- having 조건
2) 이 때 만점이란, Submissions 테이블의 score 값과 Difficulty 테이블의 score 값이 일치하는 것 -- where 조건
3) full score 받은 challenge의 개수 기준으로 내림차순 정렬, 만약 full score 받은 challenge 개수가 동일하다면 hacker_id 기준으로 오름차순 정렬 -- order by 조건

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

▶Contest Leaderboard

SELECT M.HACKER_ID, H.NAME, SUM(M.MAX) AS TOTAL_SCORE
FROM ( 
    SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS MAX
    FROM SUBMISSIONS
    GROUP BY HACKER_ID, CHALLENGE_ID) AS M -- 최대 점수를 만들어주고
    
INNER JOIN HACKERS H ON M.HACKER_ID = H.HACKER_ID -- 조인을 한다음

GROUP BY M.HACKER_ID, H.NAME

HAVING TOTAL_SCORE > 0 -- score가 0인 것은 제외

ORDER BY TOTAL_SCORE DESC, M.HACKER_ID