> For the complete documentation index, see [llms.txt](https://dshub.gitbook.io/ds-hub/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/hackerrank/sql-intermediate/top-competitors.md).

# Top Competitors

[Question Link](https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=false)

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. ![](https://s3.amazonaws.com/hr-challenge-images/19504/1458526776-67667350b4-ScreenShot2016-03-21at7.45.59AM.png)
* *Difficulty:* The *difficult\_level* is the level of difficulty of the challenge, and *score* is the score of the challenge for the difficulty level.   
* ![](https://s3.amazonaws.com/hr-challenge-images/19504/1458526915-57eb75d9a2-ScreenShot2016-03-21at7.46.09AM.png)
* *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. ![](https://s3.amazonaws.com/hr-challenge-images/19504/1458527032-f9ca650442-ScreenShot2016-03-21at7.46.17AM.png)
* *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.
*  ![](https://s3.amazonaws.com/hr-challenge-images/19504/1458527077-298f8e922a-ScreenShot2016-03-21at7.46.29AM.png)

***

**Sample Input**

*Hackers* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19504/1458527241-6922b4ad87-ScreenShot2016-03-21at7.47.02AM.png) *Difficulty* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19504/1458527265-7ad6852a13-ScreenShot2016-03-21at7.46.50AM.png) 

*Challenges* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19504/1458527285-01e95eb6ec-ScreenShot2016-03-21at7.46.40AM.png) 

*Submissions* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19504/1458527812-479a74b99f-ScreenShot2016-03-21at8.06.05AM.png)

**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 2 space-separated values.

```sql
/* 
Display 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.
*/
SELECT
    h.hacker_id,
    h.name
FROM hackers h
JOIN (
SELECT
s.*,
c.difficulty_level,
d.score AS difficulty_score,
 CASE
  WHEN d.difficulty_level = 7 AND s.score = 120 THEN 'Full'
  WHEN d.difficulty_level = 6 AND s.score = 100 THEN 'Full'
  WHEN d.difficulty_level = 5 AND s.score = 80 THEN 'Full'
  WHEN d.difficulty_level = 4 AND s.score = 60 THEN 'Full'
  WHEN d.difficulty_level = 3 AND s.score = 40 THEN 'Full'
  WHEN d.difficulty_level = 2 AND s.score = 30 THEN 'Full'
  WHEN d.difficulty_level = 1 AND s.score = 20 THEN 'Full'
  ELSE 'Partial'
 END AS completed
FROM submissions s
LEFT JOIN challenges c
ON s.challenge_id = c.challenge_id
LEFT JOIN difficulty d
ON c.difficulty_level = d.difficulty_level
LEFT JOIN hackers h
ON s.hacker_id = h.hacker_id
WHERE s.score != 0) AS t
ON h.hacker_id = t.hacker_id
WHERE t.completed = 'Full'
GROUP BY 1,2
HAVING COUNT(t.challenge_id) > 1
ORDER BY COUNT(t.challenge_id) DESC, hacker_id;
```