John Lekberg


JUNTO Practice - "Contest Leaderboard"

Discussed on 2019-09-05.

Contest Leaderboard


Solution - John

SELECT Hackers.hacker_id, name, score
    FROM Hackers
         INNER JOIN (
             SELECT hacker_id, SUM(score) AS score
               FROM (
                        SELECT hacker_id, MAX(score) AS score
                          FROM Submissions
                      GROUP BY hacker_id, challenge_id
                    ) Best_Score
           GROUP BY hacker_id
         ) Total_Score
         ON Hackers.hacker_id = Total_Score.hacker_id
   WHERE score > 0
ORDER BY score DESC, hacker_id ASC
;

Solution - Oscar

SELECT
h.hacker_id,
MAX(h.name) as name,
SUM(t.score) as score
FROM Hackers h
JOIN(
SELECT
hacker_id,
challenge_id,
MAX(score) AS score
FROM 
Submissions
GROUP BY hacker_id,challenge_id
) AS t ON h.hacker_id = t.hacker_id
WHERE score > 0
GROUP BY hacker_id
ORDER BY score desc, hacker_id asc