[SQL Runday] HackerRank - Contest Leaderboard

해커의 총 점수(total score)는 모든 챌린지의 최고 점수를 모두 합한 값이다. (sum of maximum scores for all of the challenges)

• Hacker_id, name, total score를 조회하는 쿼리를 작성해라
• total score 기준 내림차순으로 정렬, 그 후 hacker_id로 오름차순 정렬
• total score가 0인 해커들은 제외하라.

Table

Hackers
Submissions

Key points

• hacker_id, challenge_id 그룹으로 max(score)값을 조회하고,
• hacker_id 그룹으로 sum(max(score))값을 조회하면 되는 간단한 문제.
• 근데 이상하게 안풀림...

첫 시도

select h.hacker_id, h.name, sum(s.score)
from Hackers h
join Submissions s on h.hacker_id = s.hacker_id
join (select h.hacker_id, s.challenge_id, max(s.score) as max_score
        from Hackers h
        join Submissions s on h.hacker_id = s.hacker_id
        group by h.hacker_id, s.challenge_id)m
on m.hacker_id = h.hacker_id and m.challenge_id = s.challenge_id
where s.score = max_score and s.score > 0
group by h.hacker_id, h.name 
order by sum(s.score) DESC, h.hacker_id

내가 잘못 생각한게, 굳이 max_score 테이블까지 join 할 필요가 없는데 굳이 저렇게 길게 씀. 그리고 저렇게 해서 나온 값과 pass한 최종 쿼리 결과값을 비교해 보았음.

다른 점
1. 66274 Chris의 값이 내 쿼리에서는 244, 최종 쿼리에서는 152였음.
2. Chris의 score board를 알아보자.

66274 Chris 30109 7 
66274 Chris 30109 12 
66274 Chris 30109 5 
66274 Chris 30109 19 
66274 Chris 30109 9 
66274 Chris 30109 60 
66274 Chris 68420 17 
66274 Chris 68420 12 
66274 Chris 68420 30 
66274 Chris 68420 2 
66274 Chris 68420 92 
66274 Chris 68420 92 

보면 30109 챌린지의 max 값은 60이고, 68420의 max 값은 92점이 2번 제출되었다. (submission_id가 다르다.)
내 쿼리에서는 s.score 값과 max_score값이 같은 행을 가져오고, h.hacker_id, h.name 값을 기준으로 group by 하여 score 행을 SUM 한다. 그래서 60 + 92 + 92 = 244가 나왔다.
그런데 사실 submission id가 다르더라도, max(92,92)해서 92점 하나가 나왔어야 한다. 따라서 60 + 92가 되어야 함.
그렇기 때문에 score = max_score 행을 가져오면 안되고, 그냥 max_score을 score 값으로 불러와야 한다. 최종 쿼리와 같이 행을 고쳤다.

최종 쿼리

select h.hacker_id, h.name, sum(max_score)
from (select hacker_id, max(score) as max_score
      from Submissions 
      group by hacker_id, challenge_id 
     )m
join Hackers h on m.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having sum(max_score) > 0
order by sum(max_score) DESC, h.hacker_id