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

|

2235 Discussions

|

  • joshuarojas1099
     + 0 comments
     

Select H.hacker_id,H.name,sum(MS.MaxScore)Total

from Hackers H join
(Select HS.hacker_id,S.challenge_id, Max(S.score) MaxScore
from Hackers HS join Submissions S on 
 HS.hacker_id= S.hacker_id
group by HS.hacker_id,S.challenge_id) 
as MaxScore
on H.hacker_id=MS.hacker_id

group by H.hacker_id,H.name
having Total>0
order by Total DESC,H.hacker_id;
    ``

  • jcksfidelix
     + 0 comments

    The most important thing is THE SUM OF THE MAX and not SUM ALL SCORES. Good studies!

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

  • frelser_61
     + 0 comments
    WITH score AS(
SELECT
    DISTINCT hacker_id, challenge_id,
    MAX(score) OVER(PARTITION BY hacker_id, challenge_id) AS score
FROM
    Submissions
WHERE score != 0
)
SELECT
    h.hacker_id, name, SUM(score) AS score
FROM
Hackers AS h
LEFT JOIN score AS s
ON h.hacker_id = s.hacker_id
WHERE score IS NOT NULL
GROUP BY h.hacker_id, name
ORDER BY score DESC, h.hacker_id ASC

  • sourabhsubh20
     + 0 comments

    with main as ( select h.hacker_id, h.name,s.score,s.challenge_id , row_number()over(partition by h.hacker_id,s.challenge_id order by s.score desc) as rnk from hackers h join submissions s on h.hacker_id = s.hacker_id ) select hacker_id,name,sum(score) from main where rnk = 1 group by hacker_id,name having sum(score)> 0 order by 3 desc,1 asc

  • vallarasan_selv1
     + 0 comments

    is there dual submission for