[HackerRank SQL] Challenges

www.hackerrank.com/challenges/challenges/problem

Challenges | HackerRank

 

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. 

  

---

Answer

select h.hacker_id, h.name, count(*) as cnt # the total number of challenges created by each student
from hackers as h
 join
 challenges as c
 on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
# maximum number of challenges created -> include
having cnt = (select count(*) from challenges group by hacker_id order by count(*) desc limit 1) 
# more than one student created the same number of challenges
or cnt in (select cnt from (select count(*) as cnt from challenges group by hacker_id) as cc group by cnt having count(cnt) = 1)
order by cnt desc, h.hacker_id

having 부분이 가장 중요한데, 해당부분에서는 student count가 1이거나 아니면 최대값인 것들만 출력한다. 만약 count가 똑같으면 제외한다.

(select cnt from (select count(*) as cnt from challenges group by hacker_id) as cc group by cnt having count(cnt) = 1)

위 부분은 count가 유니크한것(한개인것)을 추려내는 것이다. 해당 count와 일치하는 것들만 출력하는 것이다.

Result