Contest Leaderboard Solution
Hello Friends in this article i am gone to share Hacker Rank SQL Solutions with you | Contest Leaderboard Solution
Also Visit: Challenges 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.
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.
- Hackers Table:
- Submissions Table:
4071 Rose 191 74842 Lisa 174 84072 Bonnie 100 4806 Angela 89 26071 Frank 85 80305 Kimberly 67 49438 Patrick 43
- 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.
Solution – Contest Leaderboard
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 Problems are generated by Hacker Rank but the Solutions are Provided by NYANDER.COM. All Hacker Rank SQL Solutions Shared only for Educational and Learning Purpose.