We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
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
;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Contest Leaderboard
You are viewing a single comment's thread. Return to all comments →
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 ;