TOP COMPETITORS: HACKERRANK

Problem Statement:

 Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

Tables:

1. Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
 2. Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score of the challenge for the difficulty level.
3.Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge, and difficulty_level is the level of difficulty of the challenge.
4. 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 that the submission belongs to, and score is the score of the submission.

Solution:

select i,n
from (select h.hacker_id i,h.name n,count(h.hacker_id) c
from ((Submissions s join Challenges c on s.challenge_id=c.challenge_id) join Difficulty d on d.score=s.score and d.difficulty_level=c.difficulty_level)join Hackers h on h.hacker_id=s.hacker_id
group by h.hacker_id,h.name
)
where c>1
order by c desc,i asc;

 Explanation:

  In this query we have to select the hacker_id and respective name of the hacker who scored maximum number of time full score in the challenge. To filter all the hackers with given condition we are first going to join the submission table with the challenge table where challenge_id is equal. this will give us all the submissions along with difficulty_level, score,hacker_id,score,submission_id and challenge_id. Now we will join the above joined table with the Difficulty Table on condition where score of submission and Difficulty is equal where difficulty_level is also equal. this will give us the table with all the hackers scored full score. Now we will join the Hackers table with the hacker_id and we will get the respective names of the hackers too. after that we use Aggregate function COUNT() to hacker_id we will get the number of times a hacker have scored a full score. and then putting all the above query in subquery we query the hacker_id and name of the hacker based on sorting condition given in the question. Hope this will clear the doubts. enjoy.

NOTE: the above query is written in the Oracle sql.