Contest Leaderboard

Sort by

recency

|

2235 Discussions

|

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

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

  • + 0 comments

    is there dual submission for