with as

with 테이블명 as

'as' 빼먹지 않기!!!

with cnt as (
select c.hacker_id, name, count(distinct challenge_id) 'cnt_challenge'
from hackers h
join challenges c on h.hacker_id=c.hacker_id
group by c.hacker_id, name
)

select hacker_id, name, cnt_challenge
from cnt
where cnt_challenge=(select max(cnt_challenge) from cnt)
or cnt_challenge in (select cnt_challenge from cnt group by cnt_challenge having count(hacker_id)=1)
order by cnt_challenge desc, hacker_id

 

문제 조건이 좀 의아; 동일한 도전 횟수가 없는, 유일한 도전 횟수의 학생만 출력