Top Competitors HackerRank Solution | Codeityweb

Top Competitors  HackerRank Solution

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

So the problem statement is as follows:

You have to write a query to print the id and name of the hackers who has scored maximum in more than one challenges.And order the output in decreasing order of the number of challenges and if the number of challenges are same then order them by the increasing hackerid.

 
Schema for the tables:

• Hackers:

 

• Difficulty:

• Challenges:

• Submissions:

 

Understanding Tables:

• 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

 

Solving the Question:

• 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.

 

Top Competitors  HackerRank Solution Using Where Clause:

 

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

 

Explanation:

•  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.

 

 

Top Competitors HackerRank Solution Using Joins:

 

select h.hacker_id ,h.name from Submissions as s

inner join Challenges as c on s.challenge_id=c.challenge_id
inner join Difficulty as d on c.difficulty_level=d.difficulty_level
inner join Hackers as h on h.hacker_id=s.hacker_id

where s.score=d.score

group by h.hacker_id,h.name

having count(s.hacker_id)>1

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

 

Explanation:

• It is no different than the Where clause in terms of understanding.
• We join all the tables on the basis of the common field.
• We use Where Clause  to select those hackers who has scored full score.
• Then we group them in order to find who has participated in more than 1 challenge and scored full score.
• Finally we Sort them as per the number of challenges scored max and if they are same for a particular hacker than we sort than according to the hacker_id.

 

Output:

 

Related posts:

• Placements - Hackerrank Solution
  
• Revising the Select Query 1- Hackerrank solution 
• Revising the Select Query II - Hackerrank Solution
• Pairs - Hackerrank Solution