hackerrank - Basic Join - Contest Leaderboard

这题主要用了sub-query

---

You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!

The total score of a hacker is the sum of their maximum scores for all of the challenges. 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  from your result.

Input Format

The following tables contain contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.

---

---

select t.hacker_id, max(h.name), sum(t.score) as total
from Hackers h inner join
(select hacker_id, challenge_id, max(score) as score
from Submissions
Group by hacker_id, challenge_id) t 
on h.hacker_id = t.hacker_id
Group by t.hacker_id
having total > 0
Order by total desc, t.hacker_id;

首先先找出每个学生同一个challenge的最高分数，即需要同时group by hacker id和challenge id，找到最高每个学生提交的每道题的最高分后，再找每个学生提交的所有challenge的总分数。