Contest Leaderboard: hackerrank problem (Level medium)
Problem Statement:
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 from your result.
Tables:
- 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.
Solutions:
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;
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;
Explanation:
i did not know that this problem could be so simple yet so complex. was diving into SQL afte a long time.. took me about my lifetime to solve this problem...oh god!!!
in this solution we are going to inner join as the problem states we have to get the sum of the maximum of score for same challenge_id submitted by the same hacker_id. To do so, a sub query is written in which are extracting the maximum score using the group by clause on challenge_id and hacker_id which will in turn collapse the hacker_id and challenge_id in the table based on the maximum score. after that we are going to perform inner join operation with the sub query. now that the tables are joined on the base of hacker_id and we have maximum score for respective challenge_id. we are going use sum() function to get the sum based on hacker_id. and whether the we are having the score>0 is checked in 'having' clause.
_______________________________The End__________________________________
Hoping this would be helpful to you, if not, post your problem in the comment section and i will try to get back to you as soon as possible.
tell me, how do you like this solution? and what is there that i can improve...
follow me on hackerrank: www.hackerrank.com/satyansh_sagar
_______________________________The End__________________________________
Hoping this would be helpful to you, if not, post your problem in the comment section and i will try to get back to you as soon as possible.
tell me, how do you like this solution? and what is there that i can improve...
follow me on hackerrank: www.hackerrank.com/satyansh_sagar
Comments