Contest leaderboard (grouped, aggregated, connected)

There are two tables

Hackers (hacker_id, name)

Submissions (submission_id, hacker_id, challenge_id, score)

Require output

1, output hacker in many challenges of the maximum and (each challenge may be submitted multiple times, only the maximum), and zero output

2, and descending, hacker_id ascending

Ideas:

First take hacker_id,challenge_id as the group, calculate the maximum value, then take hacker_id as the grouping, calculate the sum, finally connect with the hacker table, output the name.

In order to be able to make some optimization, hacker_id,challenge_id as a group, and hacker table for the connection, and finally hacker_id as a group, the sum

Select t2.hacker_id, H.name, T2.sum_score 
from 
(
select t1.hacker_id as hacker_id, sum (t1.max_score) as Sum _score 
from 
(
select hacker_id, challenge_id, Max (score) as Max_score to submissions Group by HACKER_ID, CH ALLENGE_ID has max (score) > 0
) T1 
Group by t1.hacker_id
) T2 inner JOIN hackers h on t2.hacker_id = H.H acker_id ORDER BY T2.sum_score Desc, t2.hacker_id;