[Hackerrank] SQL - Contest Leaderboard

문제

풀이

마지막에 order by 절을 사용할 때 t1.totalscore이라고 해서 계속 틀렸다.

select t1.hacker_id, t1.name, sum(score) as totalscore
from hackers as t1
join (select hacker_id, challenge_id, max(score) as score
     from submissions
     group by hacker_id, challenge_id) as t2
on t1.hacker_id = t2.hacker_id
group by t1.hacker_id, t1.name
having totalscore > 0
order by totalscore desc, t1.hacker_id

또는

select t1.hacker_id, t1.name, sum(score) as totalscore
from hackers as t1
join (select hacker_id, challenge_id, max(score) as score
     from submissions
     group by hacker_id, challenge_id
     having score>0) as t2
on t1.hacker_id = t2.hacker_id
group by t1.hacker_id, t1.name
order by totalscore desc, t1.hacker_id