Top Competitors — HackerRank Solutions

3 min readJan 20, 2021

In this post we are going to discuss the Top Competitors — HackerRank Solution.

So the problem statement is as follows:

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.

The following tables contain contest data:

Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score of the challenge for the difficulty level.

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.

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.

You can see that we have 4 different tables and each tables are related to each others with a foreign key.
Submissions table Related To Challenges Table BY challenge_id
Challenges table Related To Difficulty Table BY difficulty_level
Hackers table Related To Submissions Table BY hacker_id

As we need to find the id and name of hacker with full score in more than 1 challenge,that means we need to somehow compare the scores of the hacker with the score in the difficulty table.
Since both the values are in different tables we will need to relate them by either WHERE clause or By using JOINS.
We will use both the methods to see how it works .
So lets start.

select h.hacker_id,h.name from
hackers h,
challenges c ,
difficulty d,
submissions s

where h.hacker_id=s.hacker_id
and c.challenge_id=s.challenge_id
and c.difficulty_level=d.difficulty_level
and s.score=d.score

group by h.hacker_id,h.name having count(h.hacker_id)>1

order by count(s.score) desc,h.hacker_id

We first select the id and name from hackers Table.
Now as discussed earlier that tables are related to each others with foreign keys so we use it to fetch the records from different tables.
We use Group By to group the hackers who has participated in more than one challenge Since it is asked in the question.
Now the last step is to sort them , so we use order by to sort them in descending order of the count of the scores i.e number of challenges in which the hacker has got full scores , and if the count is same we sort them as per the hacker id.