HackerRank Contest Leaderboard problem solution

In this HackerRank Contest Leaderboard problem solution, You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!

The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0 from your result.

Problem solution MS SQL.

SELECT hacker_id, name, SUM(scr) as total_score
FROM
   (SELECT hackers.hacker_id, hackers.name, MAX(submissions.score) as scr, submissions.challenge_id
   FROM hackers
   JOIN submissions
   ON (submissions.hacker_id = hackers.hacker_id)
   GROUP BY hackers.hacker_id, hackers.name, submissions.challenge_id) as temp
GROUP BY hacker_id, name
HAVING SUM(scr) > 0
ORDER BY total_score DESC, hacker_id;

Problem solution in Oracle.

select c1.hacker_id, name, score from 
(select hacker_id, sum(score) score from 
(select hacker_id, challenge_id, max(score) score from submissions group by hacker_id, challenge_id) 
group by hacker_id) c1,
hackers c2
where c1.hacker_id = c2.hacker_id and score > 0
order by score desc, c1.hacker_id;

Problem solution in DB2.

SELECT
    *
FROM
(
SELECT
    S.HACKER_ID,
    H.NAME,
    SUM(S.SCORE) AS SCORET
    FROM
    (
        SELECT
            HACKER_ID,
            CHALLENGE_ID,
            MAX(SCORE) AS SCORE
        FROM SUBMISSIONS
        GROUP BY
            HACKER_ID,
            CHALLENGE_ID
    ) S
INNER JOIN HACKERS H
    ON H.HACKER_ID = S.HACKER_ID
GROUP BY
    S.HACKER_ID,
    H.NAME
HAVING SUM(S.SCORE) > 0
)
ORDER BY
    SCORET DESC,
    HACKER_ID ASC;