with hacker_result as (select h.hacker_id, h.name,s.challenge_id, max(s.score) as score from Hackers h inner join Submissions s on h.hacker_id = s.hacker_id group by s.challenge_id, h.hacker_id, h.name ) SELECT hr.hacker_id, hr.name, SUM(hr.score) AS total_score FROM hacker_result hr GROUP BY hr.hacker_id, hr.name HAVING SUM(CASE WHEN hr.score > 0 THEN 1 ELSE 0 END) > 0 ORDER BY total_score desc, hr.hacker_id asc;

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

SQL SERVER SELECT HACKERS.HACKER_ID, HACKERS.NAME, SUM(MAX_SCORE.MAXSCORE) AS TOTALSCORE FROM ( SELECT SUBMISSIONS.HACKER_ID, SUBMISSIONS.CHALLENGE_ID, MAX(SUBMISSIONS.SCORE) AS MAXSCORE FROM SUBMISSIONS GROUP BY SUBMISSIONS.HACKER_ID, SUBMISSIONS.CHALLENGE_ID ) AS MAX_SCORE JOIN HACKERS ON HACKERS.HACKER_ID = MAX_SCORE.HACKER_ID GROUP BY HACKERS.HACKER_ID, HACKERS.NAME HAVING SUM(MAX_SCORE.MAXSCORE) <> 0 ORDER BY TOTALSCORE DESC, HACKERS.HACKER_ID ASC

with final_body as( SELECT f.hacker_id,f.name,sum(f.marks) as tp FROM( select h.hacker_id,h.NAME,s.challenge_id,max(s.score)as marks from Hackers h,Submissions s where h.hacker_id=s.hacker_id group by h.NAME,h.hacker_id,s.challenge_id order by h.hacker_id,s.challenge_id )f group by f.hacker_id,f.name order by tp desc) SELECT * from final_body b where b.hacker_id not in (SELECT final_body.hacker_id from final_body where final_body.tp=0) order by b.tp desc,b.hacker_id;