# Contest Leaderboard in SQL | HackerRank Solution

Hello coders, today we are going to solve Contest Leaderboard HackerRank Solution in SQL.

## Problem

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_idname, 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.
• 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.

Sample Input

Hackers Table:

Submissions Table:

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 48062607180305, and 49438 can be similarly calculated.

## Solution – Contest Leaderboard in SQL

### MySQL

```select h.hacker_id,h.name,sum(sscore)
from Hackers h inner join (select s.hacker_id,max(score) as sscore from Submissions s group by s.hacker_id,s.challenge_id) st on h.hacker_id=st.hacker_id
group by h.hacker_id,h.name
having sum(sscore)>0
order by sum(sscore) desc,h.hacker_id asc;
```

Disclaimer: The above Problem (Contest Leaderboard) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.

### 2 thoughts on “Contest Leaderboard in SQL | HackerRank Solution”

1. select a.hacker_id,a.name,sum(b.sscore)
from Hackers as a
inner join (select hacker_id,max(score) as sscore from Submissions
group by hacker_id,challenge_id) as b
on
a.hacker_id=b.hacker_id
group by a.hacker_id,a.name
having sum(b.sscore)>0
order by sum(b.sscore) desc,a.hacker_id asc;