We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
- Prepare
- SQL
- Basic Join
- Contest Leaderboard
- Discussions
Contest Leaderboard
Contest Leaderboard
Sort by
recency
|
2235 Discussions
|
Please Login in order to post a comment
The most important thing is THE SUM OF THE MAX and not SUM ALL SCORES. Good studies!
SELECT H.hacker_id, H.name, SUM(max_scores.max_score) AS total_score FROM Hackers H JOIN ( SELECT hacker_id, challenge_id, MAX(score) AS max_score FROM Submissions S GROUP BY hacker_id, challenge_id ) max_scores ON H.hacker_id = max_scores.hacker_id GROUP BY H.hacker_id, H.name HAVING SUM(max_scores.max_score) > 0 ORDER BY total_score DESC, hacker_id ASC;
with main as ( select h.hacker_id, h.name,s.score,s.challenge_id , row_number()over(partition by h.hacker_id,s.challenge_id order by s.score desc) as rnk from hackers h join submissions s on h.hacker_id = s.hacker_id ) select hacker_id,name,sum(score) from main where rnk = 1 group by hacker_id,name having sum(score)> 0 order by 3 desc,1 asc
is there dual submission for