Contest Leaderboard

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 0 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.

Sample Input

Hackers Table:

Submissions Table:

Sample Output

4071 Rose 191
74842 Lisa 174
84072 Bonnie 100
4806 Angela 89
26071 Frank 85
80305 Kimberly 67
49438 Patrick 43

Analysis

We need a total score, defined to be the sum of max scores for all challenges. We will need to create a variable for total_score and join it with other tables.

• score of a hacker is the sum of their maximum scores for all of the challenges ==> (SELECT hacker_id, challenge_id, max(score) FROM Submissions GROUP BY hackerid, challengeid ORDER BY score ) as total_score

• print the hacker_id, name, and total score ==> SELECT h.hackerid, h.name, total_score

Query

## revised
SELECT h.hacker_id, h.name, sum(t.score) AS total_score 
FROM (SELECT hacker_id, challenge_id, max(score) AS score 
FROM Submissions GROUP BY hacker_id, challenge_id) as t 
JOIN Hackers AS h ON h.hacker_id = t.hacker_id
GROUP By t.hacker_id, h.name
HAVING total_score > 0
ORDER BY total_score DESC, hacker_id

#wrong code
SELECT h.hackerid, h.name, t.total_score 
FROM (SELECT hacker_id, challenge_id, max(score) AS total_score 
FROM Submissions GROUP BY hackerid, challengeid ) as t 
JOIN Hackers AS h ON h.hacker_id = m.hacker_id
WHERE t.total_score <> 0 #aggregation need having