Contest Leaderboard(分组、聚合、连接)

有两张表

hackers(hacker_id, name)

submissions(submission_id, hacker_id, challenge_id, score)

要求输出

1、输出hacker在多次挑战中最大值的和（每次挑战可能提交了多次，只取最大值），和为零的不输出

2、和降序、hacker_id升序

思路：

先以hacker_id,challenge_id为分组，计算最大值，再以hacker_id为分组，计算总和，最后与hacker表作连接，输出名字。

以可以作些优化，以hacker_id,challenge_id为分组，与hacker表作连接，最后以hacker_id为分组，作总和

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 from submissions group by hacker_id, challenge_id having max(score) > 0
) t1 
group by t1.hacker_id
) t2 inner join hackers h on t2.hacker_id = h.hacker_id order by t2.sum_score desc, t2.hacker_id;