SESSION NAME: Joins and Unions-1

QUESTION NAME: Challenges


TABLE DESCRIPTION:

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.

Challenges: The challenge_id is the id of the challenge,
and hacker_id is the id of the student who created the
challenge.



CODE:

SELECT H.HACKER_ID,
H.NAME,
COUNT(C.CHALLENGE_ID) AS C_COUNT
FROM HACKERS H
JOIN CHALLENGES C ON C.HACKER_ID = H.HACKER_ID
GROUP BY H.HACKER_ID, H.NAME
HAVING C_COUNT = 
    (SELECT COUNT(C2.CHALLENGE_ID) AS C_MAX
     FROM CHALLENGES AS C2
     GROUP BY C2.HACKER_ID 
     ORDER BY C_MAX DESC LIMIT 1)
OR C_COUNT IN 
    (SELECT DISTINCT C_COMPARE AS C_UNIQUE
     FROM (SELECT H2.HACKER_ID, 
                  H2.NAME, 
                  COUNT(CHALLENGE_ID) AS C_COMPARE
           FROM HACKERS H2

           JOIN CHALLENGES C ON C.HACKER_ID = H2.HACKER_ID
           GROUP BY H2.HACKER_ID, H2.NAME) COUNTS
     GROUP BY C_COMPARE
     HAVING COUNT(C_COMPARE) = 1)
ORDER BY C_COUNT DESC, H.HACKER_ID;