[코딩테스트] 해커랭크 Hackerrank SQL Contest Leaderboard 문제풀이

SQL 코딩테스트 준비를 위해 해커랭크 문제풀이를 해보려고 합니다. 서버는 MySQL을 기준으로 설정하였으며, 문제의 핵심은 [sql로 조건을 이해하고, 조건 테이블을 생성한 후 추출하거나 이 과정에서 서브쿼리]를 이해하는 것입니다. 문제 분석을 통해 정답 SQL 쿼리와 오답을 포함한 자세한 풀이 과정을 작성했습니다. 

 

 

문제 : Contest Leaderboard  (해커랭크 Hackerrank SQL)

난이도 : Medium

서버 : MySQL

테이블 이름 : Hackers, Submissions

문제 의도 : 서브쿼리 혹은 조건 테이블 생성 후 추출 

Contest Leaderboard | HackerRank

 

 

 

 

 

문제 요약  : 

• 추출 : select : hacker_id, name, total_score
• 정렬 : order by : score desc hacker_id asc
• 제외 : exclude : total score = 0
• 조건 : challenge_id 겹치면 max(scores)값만 = total_score

Input sample

Hackers

 

 

 

Submissions

 

 

 

Output sample

 

 

정답 SQL 코드 (1) : 서브쿼리  

-- (1) FROM 서브쿼리 절 

SELECT h.hacker_id, 
       h.name, 
       sum(max_score) as sum_score
FROM 
	(SELECT hacker_id, challenge_id, MAX(score) as max_score
	 FROM Submissions 
	 GROUP BY hacker_id, challenge_id 
	 ) as s
JOIN Hackers h 
ON s.hacker_id = h.hacker_id 
GROUP BY h.hacker_id, h.name
HAVING sum_score != 0
ORDER BY sum_score DESC, h.hacker_id ASC

 

 

 

다른 SQL 풀이 (2) : WITH 구문 

(그러나, WIth 구문은 해커링크 - Mysql 버전 사용시, 쿼리 실행불가..) 

 

 

WITH max_scores AS (
    SELECT hacker_id, challenge_id, MAX(score) AS max_score
    FROM Submissions 
    GROUP BY hacker_id, challenge_id 
)

SELECT h.hacker_id, 
       h.name, 
       SUM(ms.max_score) AS sum_score
FROM max_scores ms
JOIN Hackers h 
ON ms.hacker_id = h.hacker_id 
GROUP BY h.hacker_id, h.name
HAVING sum_score != 0
ORDER BY sum_score DESC, h.hacker_id ASC;

 

 

❗️ 회고록 

: 처음에 WHERE 절의 서브쿼리를 사용해 코드를 작성했지만 -> 오답 -> 이유 : 최대 점수만이 아닌 다른 점수들도 포함할 수 있는 쿼리

주의사항 :  bigquery환경 -> bigquery는 IN을 다중행, 다중열에 사용할 수 없다....JOIN 사용해야 한다..!

 

-- (1) mysql 서버 기준 - where 서브쿼리 이용 -> 오답코드 
SELECT s.hacker_id, 
       h.name,
       SUM(score) as sum_score
FROM hackers h 
JOIN submissions s 
ON h.hacker_id = s.hacker_id 
where (s.challenge_id, s.score) IN (
    SELECT challenge_id, max(score)
    FROM submissions
    GROUP BY hacker_id, challenge_id 
)
group by hacker_id, name 
having sum_score != 0 
order by sum_score desc, hacker_id asc

 

 

 

(2) where 서브쿼리 bigquery -> bigquery는 IN을 다중행, 다중열에 사용할 수 없다....JOIN 사용해야 한다..

,  max_scores AS (
    SELECT hacker_id, challenge_id, MAX(score) AS max_score
    FROM submissions
    GROUP BY hacker_id, challenge_id
)

SELECT s.hacker_id, 
       h.name,
       SUM(s.score) AS sum_score
FROM hackers h 
JOIN submissions s ON h.hacker_id = s.hacker_id 
JOIN max_scores m ON s.hacker_id = m.hacker_id AND s.challenge_id = m.challenge_id AND s.score = m.max_score
GROUP BY s.hacker_id, h.name 
HAVING sum_score != 0 
ORDER BY sum_score DESC, s.hacker_id ASC;