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 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
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 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