Hacker Rank SQL

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


 

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_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.ย 
  • 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ย 4806,ย 26071,ย 80305, andย 49438ย can be similarly calculated.

 

Solution โ€“ Contest Leaderboard

MySQL Code
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.