Challenges (HackerRank)

Challenges | HackerRank

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 sub as (
select Hackers.hacker_id, Hackers.name, count(*) as challenges_created
from Challenges join hackers on Challenges.hacker_id = hackers.hacker_id
group by Hackers.hacker_id, Hackers.name
    )

select hacker_id, name, challenges_created
from sub 
where challenges_created = (select max(challenges_created) from sub)
or
challenges_created in (select challenges_created from sub group by challenges_created having count(*) = 1)
order by challenges_created desc, hacker_id asc

-> (MS SQL) with절 사용하여 challenges_created 먼저 연산 / count(c.c) = 1을 having 절로 응용하여 challenges_created 활용

2022.09.19