Challenges

https://www.hackerrank.com/challenges/challenges

• 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.

WITH cnt AS(
SELECT h.hacker_id
     , h.name
     , COUNT(*) total_num
FROM challenges c
    INNER JOIN hackers h ON c.hacker_id = h.hacker_id  
GROUP BY h.hacker_id, h.name
)
SELECT *
FROM cnt
-- maximum number of challenges created
WHERE total_num = (SELECT MAX(total_num) FROM cnt)
-- 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
    OR total_num IN (SELECT total_num
                     FROM cnt
                     GROUP BY total_num
                     HAVING COUNT(*) = 1)                
ORDER BY total_num DESC, hacker_id