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.
  1. Prepare
  2. SQL
  3. Basic Join
  4. Contest Leaderboard
  5. Discussions

Contest Leaderboard

Sort by

recency

|

2259 Discussions

|

  • sushanth678
     + 0 comments

    Select Hacker_id,name,sum(score) as total_score from( Select * from( Select h.hacker_id,h.name,s.submission_id,s.challenge_id,s.score,Row_number() over(partition by h.hacker_id,s.challenge_id order by s.score desc) as rno from Hackers h join submissions s on h.hacker_id = s.hacker_id order by h.hacker_id desc) where score <> 0 and rno=1 ) group by Hacker_id,name order by total_score desc,hacker_id asc;

  • adeebaaaisha2005
     + 0 comments
    SELECT s.hacker_id, h.name, SUM(s.max_score) AS total_score
FROM (
  SELECT hacker_id, challenge_id, MAX(score) AS max_score
  FROM Submissions
  GROUP BY hacker_id, challenge_id
) s
JOIN Hackers h ON s.hacker_id = h.hacker_id
GROUP BY s.hacker_id, h.name
HAVING SUM(s.max_score) > 0
ORDER BY total_score DESC, s.hacker_id ASC;

  • akangkhasarkar
     + 0 comments

    **MYSQL** SELECT h.hacker_id, h.name, SUM(maxScore) AS totalScore FROM hackers h JOIN ( SELECT MAX(score) AS maxScore, hacker_id, challenge_id FROM submissions GROUP BY hacker_id, challenge_id ) AS sub ON sub.hacker_id = h.hacker_id GROUP BY h.hacker_id, h.name HAVING totalScore > 0 order by totalScore desc,h.hacker_id;

  • swethascybsec
     + 0 comments

    MS SQL Server

    WITH maxscores AS ( SELECT hacker_id, challenge_id, MAX(score) AS score FROM submissions GROUP BY hacker_id, challenge_id )

    SELECT m.hacker_id, h.name,SUM(m.score) AS score FROM maxscores m INNER JOIN hackers h ON h.hacker_id = m.hacker_id WHERE m.score <>0 GROUP BY m.hacker_id, h.name ORDER BY SUM(m.score)DESC, m.hacker_id ASC ;

  • sahil_skr15
     + 0 comments

    SELECT h.hacker_id, h.name, t.total_score FROM Hackers h JOIN ( SELECT hacker_id, SUM(max_score) AS total_score FROM ( SELECT hacker_id, challenge_id, MAX(score) AS max_score FROM Submissions GROUP BY hacker_id, challenge_id ) AS max_scores GROUP BY hacker_id ) t ON h.hacker_id = t.hacker_id WHERE t.total_score > 0 ORDER BY t.total_score DESC, h.hacker_id ASC;