HackerRank MySQL - 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.



  1. SELECT H.HACKER_ID,
  2. H.NAME,
  3. COUNT(C.CHALLENGE_ID) AS TOTAL
  4. FROM HACKERS H,
  5. CHALLENGES C
  6. WHERE H.HACKER_ID=C.HACKER_ID
  7. GROUP BY H.HACKER_ID,
  8. H.NAME
  9. HAVING COUNT(C.CHALLENGE_ID) IN
  10. (SELECT MAX(TOTAL)
  11. FROM
  12. (SELECT COUNT(*) AS TOTAL
  13. FROM CHALLENGES
  14. GROUP BY HACKER_ID))
  15. OR COUNT(C.CHALLENGE_ID) IN
  16. (SELECT TOTAL
  17. FROM
  18. (SELECT COUNT(*) AS TOTAL
  19. FROM CHALLENGES
  20. GROUP BY HACKER_ID)
  21. GROUP BY TOTAL
  22. HAVING COUNT(TOTAL)=1)
  23. ORDER BY COUNT(C.CHALLENGE_ID) DESC, H.HACKER_ID;

download android app