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

|

2243 Discussions

|

  • sourav_992001
     + 0 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 ;

  • sarikavk826
     + 0 comments

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

  • mudit4hire
     + 0 comments
    WITH CTE1 as (
SELECT h.name as name,
       s.hacker_id as hid,
       s.challenge_id as cid,
       ROW_NUMBER() OVER (PARTITION BY s.hacker_id,s.challenge_id ORDER BY score DESC)   as rnk,
       s.score 
FROM Hackers as h
JOIN Submissions AS s ON h.hacker_id = s.hacker_id
),

CTE2 AS (
SELECT hid,
       name,
       score
FROM CTE1
WHERE rnk = 1
)

SELECT hid,
       name,
       SUM(score) AS max_score from CTE2
GROUP BY hid,name
HAVING SUM(score)>0
ORDER BY max_score DESC,hid

  • tevin_hislop
     + 0 comments

    Using Oracle:

        (SELECT 
        h.hacker_id,
        h.name,
        s.challenge_id, 
        MAX(score) as best_score 
     FROM Hackers h 
     JOIN Submissions s 
        ON h.hacker_id = s.hacker_id 
     GROUP BY h.hacker_id, h.name, s.challenge_id
    ) 
SELECT hacker_id, name, SUM(best_score) as total_score
FROM max_scores
GROUP BY hacker_id, name
HAVING SUM(best_score) > 0
ORDER BY total_score DESC, hacker_id ASC;

  • imamityadav1999
     + 0 comments

    /* Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. */

    select * from ( select hacker_id, name, sum(max_score) as total_score from ( select s.hacker_id, h.name, s.challenge_id , max(s.score) as max_score from submissions s join hackers h on s.hacker_id = h.hacker_id group by s.hacker_id, h.name, s.challenge_id --HAVING MAX(S.SCORE)>0 ) group by hacker_id, name ) WHERE TOTAL_SCORE <>0 order by total_score desc, hacker_id asc ;