Hacker Rank SQL

Top Competitors Solution

Hello Friends in this article i am gone to share Hacker Rank SQL Solutions with you | Top Competitors Solution


Also Visit:ย  The Report Solution


 

Problem

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.

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

Sample Input

Hackersย Table:ย ย Difficultyย Table:ย ย Challengesย Table:ย ย Submissionsย Table:ย 

Sample Output

90411 Joe

Explanation

Hackerย 86870ย got a score ofย 30ย for challengeย 71055ย with a difficulty level ofย 2, soย 86870ย earned a full score for this challenge.

Hackerย 90411ย got a score ofย 30ย for challengeย 71055ย with a difficulty level ofย 2, soย 90411ย earned a full score for this challenge.

Hackerย 90411ย got a score ofย 100ย for challengeย 66730ย with a difficulty level ofย 6, soย 90411ย earned a full score for this challenge.

Only hackerย 90411ย managed to earn a full score for more than one challenge, so we print the theirย hacker_idย andย nameย asย ย space-separated values.

Solution โ€“ Top Competitors

MySQL Code
SELECT H.hacker_id, 
H.name 
FROM submissions S 
JOIN challenges C 
ON S.challenge_id = C.challenge_id 
JOIN difficulty D 
ON C.difficulty_level = D.difficulty_level 
JOIN hackers H 
ON S.hacker_id = H.hacker_id 
AND S.score = D.score 
GROUP BY H.hacker_id, 
H.name 
HAVING Count(S.hacker_id) > 1 
ORDER BY Count(S.hacker_id) DESC, S.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.