WITH CTE AS( SELECT S.hacker_id,H.name,S.submission_id,S.challenge_id,SUM(S.score) AS TotalScore, ROW_NUMBER() OVER(PARTITION BY S.hacker_id,S.challenge_id ORDER BY SUM(S.score) DESC) AS r FROM Submissions AS S INNER JOIN Hackers AS H ON S.hacker_id=H.hacker_id GROUP BY S.hacker_id,H.name,S.submission_id,S.challenge_id) ,CTE2 AS( SELECT hacker_id,name,TotalScore FROM CTE WHERE r=1 AND TotalScore!=0) ,CTE3 AS( SELECT hacker_id,name,SUM(CTE2.TotalScore) AS TS FROM CTE2 GROUP BY hacker_id,name) SELECT hacker_id,name,TS FROM CTE3 ORDER BY TS DESC, hacker_id ASC

My Code:

select h.hacker_id,h.name,sum(s.max_score) as total_score from hackers h join (select ss.hacker_id,ss.challenge_id,max(score) as max_score from submissions ss group by ss.hacker_id,ss.challenge_id ) as s on h.hacker_id=s.hacker_id group by h.hacker_id,h.name having total_score>0 order by total_score DESC,h.hacker_id ASC

with abc as (select hacker_id,challenge_id, max(score) as max_score_challenge from submissions group by hacker_id,challenge_id) select h.hacker_id,h.name, sum(a.max_score_challenge) from hackers h inner join abc a on h.hacker_id=a.hacker_id group by h.hacker_id,h.name having sum(a.max_score_challenge)>0 order by 3 desc, 1;

MY SQL: SELECT A.hacker_id, A.name, SUM(B.score) AS total_score FROM Hackers A JOIN ( SELECT hacker_id, challenge_id, MAX(score) AS score FROM Submissions GROUP BY challenge_id, hacker_id ) B ON A.hacker_id = B.hacker_id GROUP BY A.hacker_id, A.name HAVING total_score > 0 ORDER BY total_score DESC, A.hacker_id ASC;

WITH filter AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY hacker_id, challenge_id ORDER BY score DESC) AS rn FROM submissions ) SELECT h.hacker_id, h.name, SUM(f.score) FROM Hackers h JOIN filter f ON h.hacker_id = f.hacker_id WHERE f.rn = 1 GROUP BY h.hacker_id, h.name HAVING SUM(f.score) > 0 ORDER BY SUM(f.score) DESC, h.hacker_id ;