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

|

2263 Discussions

|

  • knocknotknock
     + 0 comments
    WITH cte AS (
        SELECT
            h.hacker_id,
            h.name,
            MAX(s.score) as max_scr
        FROM
            hackers h
            INNER JOIN submissions s ON s.hacker_id = h.hacker_id
        WHERE
            s.score != 0
        GROUP BY
            h.hacker_id, h.name, s.challenge_id)

SELECT
    hacker_id,
    name,
    SUM(max_scr)
FROM
    cte
GROUP BY
    hacker_id, name
ORDER BY
    SUM(max_scr) DESC, hacker_id

  • HarshPrashar1
     + 0 comments

    SELECT hacker_id, (SELECT name FROM Hackers WHERE Hackers.hacker_id = S.hacker_id) AS name, SUM(score) AS total_score FROM ( SELECT hacker_id, challenge_id, MAX(score) AS score FROM Submissions GROUP BY hacker_id, challenge_id ) AS S GROUP BY hacker_id HAVING SUM(score) > 0 ORDER BY total_score DESC, hacker_id ASC;

  • chandrasunil93
     + 0 comments
    with pr as
(
    select h.name, s.hacker_id, s.challenge_id, s.score,
    ROW_NUMBER() over (partition by h.hacker_id, s.challenge_id order by score desc) as crnk
    from hackers h join submissions s on h.hacker_id = s.hacker_id
),
dr as 
(
    select name, hacker_id, SUM(score) as scr
    from pr
    where crnk = 1
    group by name, hacker_id
)

select hacker_id, name, scr
from dr 
where scr <> 0
order by scr desc, hacker_id asc

  • nisarbaig1990
     + 0 comments

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

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