WITH CTE1 as (
SELECT h.name as name,
       s.hacker_id as hid,
       s.challenge_id as cid,
       ROW_NUMBER() OVER (PARTITION BY s.hacker_id,s.challenge_id ORDER BY score DESC)   as rnk,
       s.score 
FROM Hackers as h
JOIN Submissions AS s ON h.hacker_id = s.hacker_id
),

CTE2 AS (
SELECT hid,
       name,
       score
FROM CTE1
WHERE rnk = 1
)

SELECT hid,
       name,
       SUM(score) AS max_score from CTE2
GROUP BY hid,name
HAVING SUM(score)>0
ORDER BY max_score DESC,hid