Contest Leaderboard

Sort by

recency

|

2157 Discussions

|

  • + 0 comments

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

  • + 0 comments
    with max_challenge_scores as (
        select 
            h.hacker_id
            ,h.name
            ,max(s.score) as max_challenge_score
        from hackers h
        left join submissions s on h.hacker_id = s.hacker_id
        group by h.hacker_id, h.name, challenge_id
    )
    
    select
        hacker_id
        ,name
        ,sum(max_challenge_score) as total_score
    from max_challenge_scores 
    group by hacker_id, name
    having sum(max_challenge_score) > 0
    order by total_score desc, hacker_id asc
    
  • + 0 comments

    MS SQL server whats wrong in this code? with abc as ( select hackers.hacker_id, hackers.name, submissions.challenge_id, submissions.score, dense_rank() over(partition by hackers.hacker_id,submissions.challenge_id order by submissions.score desc) as ranking from hackers join submissions on hackers.hacker_id = submissions.hacker_id ) SELECT hacker_id, name, SUM(score) AS totalsum FROM abc WHERE ranking = 1 GROUP BY hacker_id, name HAVING SUM(score) > 0 ORDER BY totalsum desc,hacker_id;

  • + 0 comments
    SELECT challenge_highs.hacker_id, hackers.name, SUM(max_score) as total_score
    FROM 
        (SELECT hacker_id, MAX(score)as max_score
        FROM submissions 
        GROUP BY hacker_id, challenge_id
        ) as   challenge_highs
    JOIN hackers
    ON hackers.hacker_id = challenge_highs.hacker_id
    GROUP BY challenge_highs.hacker_id, hackers.name
    HAVING total_score > 0
    ORDER BY total_score DESC, challenge_highs.hacker_id;
    
  • + 0 comments

    MY SQL

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