- Prepare
- SQL
- Basic Join
- Contest Leaderboard
- Discussions
Contest Leaderboard
Contest Leaderboard
+ 0 comments Select hacker_id, name, sum(t) as total_score from (Select submissions.hacker_id, Hackers.name, max(Submissions.score) as t, submissions.challenge_id from Submissions inner Join Hackers on Submissions.hacker_id=Hackers.hacker_id group by Submissions.hacker_id, Hackers.name, submissions.challenge_id) as bm group by hacker_id, name HAVING total_score >0 order by total_score desc, hacker_id asc;
+ 0 comments Select hacker_id, name, sum(t) as total_score from (Select submissions.hacker_id, Hackers.name, max(Submissions.score) as t, submissions.challenge_id from Submissions inner Join Hackers on Submissions.hacker_id=Hackers.hacker_id group by Submissions.hacker_id, Hackers.name, submissions.challenge_id) as bm group by hacker_id, name HAVING total_score >0 order by total_score desc, hacker_id asc;
+ 0 comments It took me a bit of time to understand why my solution didn't work. Each hacker can make more than one submission per challenge. So you have to find the maximum score per
challenge_id
and then sum up those max scores from all challenges the hacker participated in. Also, reading carefully helps. You have to excludesum = 0
, notscore = 0
.
+ 0 comments select a.hacker_id,a.name,sum(a.scr) from ( select hack.hacker_id,hack.name,max(sub.score) as scr from Hackers hack inner join Submissions sub on hack.hacker_id=sub.hacker_id group by hack.hacker_id,hack.name,sub.challenge_id ) a group by a.hacker_id,a.name having sum(a.scr) <> 0 order by sum(a.scr) desc,a.hacker_id asc
+ 0 comments SELECT h.hacker_id, h.name, SUM(t.max_score) total FROM hackers h INNER JOIN (SELECT hacker_id, challenge_id, MAX(score) max_score FROM submissions GROUP BY hacker_id, challenge_id) t ON h.hacker_id = t.hacker_id GROUP BY h.hacker_id, h.name HAVING total != 0 ORDER BY total DESC, h.hacker_id
Sort 1362 Discussions, By:
Please Login in order to post a comment