# 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;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dshub.gitbook.io/ds-hub/sql/sql-practice/popular-websites-for-sql-practice/hackerrank/sql-intermediate/top-competitors.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.