Contest Leaderboard (HackerRank)

Contest Leaderboard | HackerRank

Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0 from your result.

풀이

제출한 답

with sub as(
select hacker_id, challenge_id, max(score) as sc
from submissions
group by hacker_id, challenge_id
)

select sub.hacker_id, hackers.name, sum(sc) as total_score
from sub join hackers on sub.hacker_id = hackers.hacker_id
group by sub.hacker_id, hackers.name
having sum(sc) <> 0
order by total_score desc, sub.hacker_id asc

-> 문제를 여러 번 푼 경우 최대 값들만 구해서 sum 해 줌

2022.10.04