# Challenges

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

Julia asked her students to create some coding challenges. Write a query to print the *hacker\_id*, *name*, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by *hacker\_id*. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

**Input Format**

The following tables contain challenge 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/19506/1458521004-cb4c077dd3-ScreenShot2016-03-21at6.06.54AM.png)

* *Challenges:* The *challenge\_id* is the id of the challenge, and *hacker\_id* is the id of the student who created the challenge.

 ![](https://s3.amazonaws.com/hr-challenge-images/19506/1458521079-549341d9ec-ScreenShot2016-03-21at6.07.03AM.png)

***

**Sample Input 0**

*Hackers* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19506/1458521384-34c6866dae-ScreenShot2016-03-21at6.07.15AM.png) *Challenges* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19506/1458521410-befa8e1cd9-ScreenShot2016-03-21at6.07.25AM.png)

**Sample Output 0**

```
21283 Angela 6
88255 Patrick 5
96196 Lisa 1
```

**Sample Input 1**

*Hackers* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19506/1458521469-87036deea3-ScreenShot2016-03-21at6.07.48AM.png) *Challenges* Table: ![](https://s3.amazonaws.com/hr-challenge-images/19506/1458521490-358215cf0b-ScreenShot2016-03-21at6.07.58AM.png)

**Sample Output 1**

```
12299 Rose 6
34856 Angela 6
79345 Frank 4
80491 Patrick 3
81041 Lisa 1
```

**Explanation**

For *Sample Case 0*, we can get the following details: \
![](https://s3.amazonaws.com/hr-challenge-images/19506/1458521677-fd04c384c0-ScreenShot2016-03-21at6.07.38AM.png) \
Students  5077 and **62743** both created **4** challenges, but the maximum number of challenges created is **6** so these students are excluded from the result.

For *Sample Case 1*, we can get the following details: \
![](https://s3.amazonaws.com/hr-challenge-images/19506/1458521836-24039e7523-ScreenShot2016-03-21at6.08.08AM.png) \
Students **12299** and **34856** both created  **6** challenges. Because **6** is the maximum number of challenges created, these students are included in the result.

```sql
/*
Write a query to print the hacker_id, name, and the total number of challenges created 
by each student. Sort your results by the total number of challenges in descending order.
If more than one student created the same number of challenges, then sort the result by
hacker_id. If more than one student created the same number of challenges and the count
is less than the maximum number of challenges created, then exclude those students from
the result.
*/

WITH temp AS (
    SELECT 
        h.hacker_id, 
        h.name, 
        COUNT(c.challenge_id) cnt
    FROM Hackers h 
    INNER JOIN Challenges c
    ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id, h.name
    ORDER BY cnt DESC, h.hacker_id
    )

SELECT * 
FROM temp
WHERE (temp.cnt = (SELECT MAX(cnt) FROM temp)) OR 
      (1 = (SELECT COUNT(cnt) FROM temp u WHERE temp.cnt = u.cnt));
      
-- OR

WITH rankings AS (
    SELECT
        h.hacker_id,
        h.name,
        COUNT(c.challenge_id) AS total_challenges,
        COUNT(*) OVER(PARTITION BY COUNT(c.challenge_id)) AS total_chall_count
    FROM hackers h
    LEFT JOIN challenges c
    ON h.hacker_id = c.hacker_id
    GROUP BY 1,2
    ORDER BY COUNT(c.challenge_id) DESC, hacker_id
    )
SELECT
    hacker_id,
    name,
    total_challenges
FROM rankings 
WHERE total_challenges = (SELECT MAX(total_challenges) FROM rankings)
    OR total_chall_count = 1
```


---

# 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/challenges.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.