SELECT x.id, x.nam, SUM(x.scr) FROM( SELECT s.challenge_id AS ch_id, h.hacker_id AS id, h.name AS nam, MAX(s.score) AS scr FROM Hackers h LEFT JOIN Submissions s ON s.hacker_id = h.hacker_id GROUP BY s.challenge_id, h.hacker_id, h.name HAVING MAX(s.score) <> 0

) AS x GROUP BY x.id, x.nam ORDER BY SUM(x.scr) DESC, x.id

SELECT h.hacker_id, h.name, SUM(max_score) AS total_score FROM hackers h JOIN ( SELECT hacker_id, challenge_id, MAX(score) AS max_score FROM submissions GROUP BY hacker_id, challenge_id
) best_scores ON h.hacker_id = best_scores.hacker_id GROUP BY h.name, h.hacker_id HAVING SUM(max_score) > 0 ORDER BY total_score DESC, hacker_id ASC

SELECT H.hacker_id, H.name, SUM(M.max_score) AS T_score FROM ( SELECT hacker_id, challenge_id, MAX(score) AS max_score FROM Submissions GROUP BY hacker_id, challenge_id ) AS M JOIN Hackers H ON M.hacker_id = H.hacker_id GROUP BY H.hacker_id, H.name HAVING T_score > 0 ORDER BY T_score DESC, H.hacker_id;

with cte_sub as ( select hacker_id,challenge_id,max(score) as max_score from submissions group by hacker_id,challenge_id ) select a.hacker_id, a.name,sum(max_score) as tot_score FROM hackers a join cte_sub b on a.hacker_id = b.hacker_id group by a.hacker_id,a.name having sum(max_score)>0 order by tot_score desc,a.hacker_id

select hacker_id,name,sum(total) from (select h.hacker_id,name,challenge_id,max(score) as total from Hackers h left join submissions s on s.hacker_id = h.hacker_id group by h.hacker_id,name,challenge_id ) as N group by hacker_id,name having sum(total) > 0 order by sum(total) desc,hacker_id asc