with maxscore as ( select s.hacker_id, max(s.score) as max_score from submissions s group by s.hacker_id,s.challenge_id ), totalscore as( select m.hacker_id, sum(m.max_score)as totalscore from maxscore m group by m.hacker_id ) select h.hacker_id,h.name,ts.totalscore from hackers h join totalscore ts on h.hacker_id=ts.hacker_id where ts.totalscore > 0 order by ts.totalscore desc, h.hacker_id asc;

ngoccth_SQL SERVER: WITH table_1 AS ( SELECT sub.hacker_id, name, challenge_id, score , ROW_NUMBER () OVER (PARTITION BY sub.hacker_id, challenge_id ORDER BY score DESC) AS row_num FROM Submissions AS Sub JOIN Hackers AS Hac ON Sub.hacker_id = Hac.hacker_id ) SELECT hacker_id, name , SUM (score) FROM table_1 WHERE row_num = 1 GROUP BY hacker_id, name HAVING SUM (score) != 0 ORDER BY SUM (score) DESC, hacker_id

with tscore as( select h.hacker_id as hck, h.name as nm, s.challenge_id as chl, max(s.score) as scr from Hackers h join Submissions s on h.hacker_id = s.hacker_id group by h.hacker_id, h.name, s.challenge_id )

select hck, nm, sum(scr) as tscr from tscore group by hck, nm having sum(scr) !=0 order by tscr desc, hck

These questions are BS. Our code should run against the sample input shown when clicked on "Run Code" to verify our potential solution. Getting an output with a test case knowhere close is frustrating.

with temp as ( select distinct h.hacker_id as hacker_id, name, challenge_id , max(score) over (partition by h.hacker_id, challenge_id) as max_score from hackers h join submissions s on s.hacker_id = h.hacker_id )

select distinct hacker_id, name, sum(max_score) as total_score from temp group by hacker_id, name having sum(max_score) <> 0 order by total_score desc, hacker_id asc