[HackerRank] Challenges

/*
1. hacker id, name, total number of challneges
2. sort by challenges desc, hacker id 
3. the count less tahn than the maximum number of challenges -> exclude
*/

with t1 as
(
select hacker_id, count(*) as num
from challenges 
group by hacker_id
)


select h.hacker_id, h.name, t1.num
from hackers as h
    inner join t1 on h.hacker_id = t1.hacker_id
where t1.num = (select max(num)
               from t1
               )
or t1.num in (select num
                from t1
                group by num
                having count(*) = 1)
order by t1.num desc, t1.hacker_id

with 를 사용하면 자주 쓰는 쿼리를 단축할 수 있다.

SQL은 하나씩 차근하게 풀어야한다.

안 그러면 머리가 꼬인다...

 

(문제 출처 - https://www.hackerrank.com/domains/sql)