## DEV Community

Retiago Drago

Posted on • Updated on

# Top Competitors | HackerRank | MSSQL

## The Problem

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

Sample:

## The Output

Sample:

`90411 Joe`

### Explanation

1. 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.
2. 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.
3. 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.
4. Only hacker 90411 managed to earn a full score for more than one challenge, so we print their `hacker_id` and `name` as 2 space-separated values.

## The Solution

Observe the relationship across the table. From the input sample and the explanation, we can conclude that `hacker_id` column in `Challenges` table doesn't serve any purpose in this problem (purple annotations).

So, we join these 4 tables together like the following:

1. Join `Submissions` table with `Hackers` table on both `hacker_id`
2. Join `Challenges` table with `Submissions` table on both `challenge_id`
3. Join `Difficulty` table with `Challenges` table on both `difficulty_level`
4. Join `Difficulty` table with `Submissions` table on both `score` to get whoever gets the top score of each challenge

### The Code

``````SELECT
h.hacker_id, h.name
FROM
hackers h
JOIN submissions s ON h.hacker_id = s.hacker_id
JOIN challenges c ON s.challenge_id = c.challenge_id
JOIN difficulty d ON c.difficulty_level = d.difficulty_level AND s.score = d.score
GROUP BY
h.hacker_id,
h.name
HAVING
COUNT(c.challenge_id) > 1
ORDER BY
COUNT(c.challenge_id) DESC,
h.hacker_id
``````

Original Source

Let's be friend & support meš