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 1362 Discussions, By:

  • mahajanbeauty
     + 0 comments

    Select hacker_id, name, sum(t) as total_score from (Select submissions.hacker_id, Hackers.name, max(Submissions.score) as t, submissions.challenge_id from Submissions inner Join Hackers on Submissions.hacker_id=Hackers.hacker_id group by Submissions.hacker_id, Hackers.name, submissions.challenge_id) as bm group by hacker_id, name HAVING total_score >0 order by total_score desc, hacker_id asc;

  • mahajanbeauty
     + 0 comments

    Select hacker_id, name, sum(t) as total_score from (Select submissions.hacker_id, Hackers.name, max(Submissions.score) as t, submissions.challenge_id from Submissions inner Join Hackers on Submissions.hacker_id=Hackers.hacker_id group by Submissions.hacker_id, Hackers.name, submissions.challenge_id) as bm group by hacker_id, name HAVING total_score >0 order by total_score desc, hacker_id asc;

  • anatolju
     + 0 comments

    It took me a bit of time to understand why my solution didn't work. Each hacker can make more than one submission per challenge. So you have to find the maximum score per challenge_id and then sum up those max scores from all challenges the hacker participated in. Also, reading carefully helps. You have to exclude sum = 0, not score = 0.

  • AmrutaNB
     + 0 comments
    select a.hacker_id,a.name,sum(a.scr) from
(
    select hack.hacker_id,hack.name,max(sub.score) as scr
    from Hackers hack
    inner join Submissions sub
    on hack.hacker_id=sub.hacker_id
    group by hack.hacker_id,hack.name,sub.challenge_id
) a
group by a.hacker_id,a.name
having sum(a.scr) <> 0
order by sum(a.scr) desc,a.hacker_id asc

  • scientist_g
     + 0 comments
    SELECT h.hacker_id, h.name, SUM(t.max_score) total
    FROM hackers h 
        INNER JOIN (SELECT hacker_id, challenge_id, MAX(score) max_score
                    FROM submissions
                    GROUP BY hacker_id, challenge_id) t
            ON h.hacker_id = t.hacker_id
    GROUP BY h.hacker_id, h.name
    HAVING total != 0
    ORDER BY total DESC, h.hacker_id
Load more conversations