> For the complete documentation index, see [llms.txt](https://dshub.gitbook.io/ds-hub/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/hackerrank/sql-intermediate/contest-leaderboard.md). # Contest Leaderboard [Question Link](https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true) 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. **Input Format** The following tables contain contest data: * *Hackers:* The *hacker\_id* is the id of the hacker, and *name* is the name of the hacker. ![](https://s3.amazonaws.com/hr-challenge-images/19503/1458522826-a9ddd28469-ScreenShot2016-03-21at6.40.27AM.png) * *Submissions:* The *submission\_id* is the id of the submission, *hacker\_id* is the id of the hacker who made the submission, *challenge\_id* is the id of the challenge for which the submission belongs to, and *score* is the score of the submission. ![](https://s3.amazonaws.com/hr-challenge-images/19503/1458523022-771511df90-ScreenShot2016-03-21at6.40.37AM.png) **Sample Input** *Hackers* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19503/1458523374-7ecc39010f-ScreenShot2016-03-21at6.51.56AM.png) *Submissions* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19503/1458523388-0896218137-ScreenShot2016-03-21at6.51.45AM.png) **Sample Output** ``` 4071 Rose 191 74842 Lisa 174 84072 Bonnie 100 4806 Angela 89 26071 Frank 85 80305 Kimberly 67 49438 Patrick 43 ``` **Explanation** Hacker *4071* submitted solutions for challenges *19797* and *49593*, so the total score = **95 + max(43,96) = 191 .** Hacker *74842* submitted solutions for challenges *19797* and *63132*, so the total score = **max(98,5) + 76 = 174** Hacker *84072* submitted solutions for challenges *49593* and *63132*, so the total score = **100 + 0 = 100 .** The total scores for hackers *4806*, *26071*, *80305*, and *49438* can be similarly calculated. 
/*
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.
 */
SELECT *
FROM(
    SELECT 
        t.hacker_id, 
        name, 
        SUM(max_scores) AS total_score
    FROM 
        (SELECT 
            hacker_id, 
            challenge_id, 
            MAX(score) AS max_scores
        FROM Submissions s
        GROUP BY hacker_id, challenge_id) AS t
    JOIN Hackers h USING (hacker_id) 
    GROUP BY t.hacker_id, name
    ORDER BY SUM(max_scores) DESC, t.hacker_id ASC) AS a
WHERE total_score <> 0

--OR
SELECT
    t.hacker_id,
    h.name,
    SUM(max_score) AS total_score
FROM
    (SELECT
        hacker_id,
        challenge_id,
        MAX(score) AS max_score
    FROM submissions
    GROUP BY 1,2
    HAVING MAX(score) > 0) AS t
JOIN hackers h
ON t.hacker_id = h.hacker_id
GROUP BY t.hacker_id, h.name
ORDER BY SUM(max_score) DESC, hacker_id