45. Challenges: Solution

---

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.

The following tables contain challenge data:

HACKERS: The hacker_id is the id of the hacker, and name is the name of the hacker.

CHALLENGES: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge.

Sample Input 0

Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1

Sample Input 1

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:

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:

Students 12299 and 34856 both created 6 challenges. Because 6 is the maximum number of challenges created, these students are included in the result.

We will build the solution to this problem gradually in three steps as follows:

Step 1: We first print the HACKER_ID, NAME and the total number of challenges created by each student, sorting the results by the total number of challenges in descending order. This is easy.

SELECT C.HACKER_ID, H.NAME, COUNT(C.CHALLENGE_ID) CNT
FROM HACKERS H, CHALLENGES C
WHERE H.HACKER_ID = C.HACKER_ID 
GROUP BY C.HACKER_ID, H.NAME
ORDER BY CNT DESC;

If more than one student created the same number of challenges, then we must sort the result by HACKER_ID. Hence we improve the abovee query as follows:

SELECT C.HACKER_ID, H.NAME, COUNT(C.CHALLENGE_ID) CNT
FROM HACKERS H, CHALLENGES C
WHERE H.HACKER_ID = C.HACKER_ID 
GROUP BY C.HACKER_ID, H.NAME
ORDER BY CNT DESC, C.HACKER_ID;

The last sentence of the problem statement needs a closer look: 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.

We can exclude such students from the results by first blindly excluding all those students who created the same number of challenges and then adding back those students who created the maximum number of challenges $N$, regardless of whether there are more than one such student. This can be accomplished using the following two steps:

1. If more than one student created the same number of challenges, then exclude all such students.
2. However, add back those students who created the maximum number of challenges $N$, regardless of whether there are more than one such student.

We will compute (1) and (2) in Step 2 and Step 3 respectively. Note that we can use an OR clause to add back the rows from (2) above.

Step 2: We first want to (blindly) exclude those students who created the same number of challenges.

This means, for any two different students, do not include them if the count of the challenges created is equal. We can exclude these rows from our initial query by adding a HAVING clause condition to our initial query as follows:

SELECT C.HACKER_ID, H.NAME, COUNT(C.CHALLENGE_ID) CNT
FROM HACKERS H, CHALLENGES C
WHERE H.HACKER_ID = C.HACKER_ID 
GROUP BY C.HACKER_ID, H.NAME
HAVING COUNT(C.CHALLENGE_ID) NOT IN (SELECT COUNT(C2.CHALLENGE_ID)
                                         FROM CHALLENGES C2
                                         GROUP BY C2.HACKER_ID
                                         HAVING C2.HACKER_ID <> C.HACKER_ID);

The quantity COUNT(C.CHALLENGE_ID) in the HAVING clause refers to the count of challenges from the top-most query block. The HAVING clause therefore excludes from the original query results all those students who happen to have created the same number of challenges.

Step 3: Now we want to add back those removed students, if they happened to have created the maximum number of challenges.

The following query returns the maximum number of challenges created:

SELECT MAX(C) N
FROM (SELECT COUNT(CHALLENGE_ID) C
      FROM CHALLENGES
      GROUP BY HACKER_ID);

We will now add back all those students who created the maximum number of challenges $N$ using the following condition in the HAVING clause:

... HAVING COUNT(C.CHALLENGE_ID) = (SELECT MAX(C) 
                                    FROM (SELECT COUNT(CHALLENGE_ID) C 
                                          FROM CHALLENGES
                                          GROUP BY HACKER_ID))

Putting all of the above together, we can construct our final query as follows:

SELECT C.HACKER_ID, H.NAME, COUNT(C.CHALLENGE_ID) CNT
FROM HACKERS H, CHALLENGES C
WHERE H.HACKER_ID = C.HACKER_ID 
GROUP BY C.HACKER_ID, H.NAME
HAVING COUNT(C.CHALLENGE_ID) NOT IN (SELECT COUNT(C2.CHALLENGE_ID)
                                     FROM CHALLENGES C2
                                     GROUP BY C2.HACKER_ID
                                     HAVING C2.HACKER_ID <> C.HACKER_ID)
       OR
       COUNT(C.CHALLENGE_ID) = (SELECT MAX(C) FROM 
                                (SELECT COUNT(CHALLENGE_ID) C 
                                 FROM CHALLENGES
                                 GROUP BY HACKER_ID))
ORDER BY CNT DESC, C.HACKER_ID;

---

Back to problems

---

anusha-murali.github.io