[문제풀이] Hacker Rank - Challenges

출처

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.

 

문제에 대한 해석

조회 : hacker_id, name, 해커1명당 만든 챌린지의 수

정렬 : 챌린지 수에 따른 내림차순과 같은 챌린지 수가 있다면 아이디 순

조건 : 챌린지의 수가 동일한 학생이 여러명 있을 경우, 해당 챌린지 수가 최대 챌린지 수보다 작다면 모두 제거하라

풀이(MYSQL)

-- 오답
with base as (
    select h.hacker_id, h.name, count(c.challenge_id) as cnt
    ,row_number()over(partition by count(c.challenge_id) order by hacker_id ) rk 
from Hackers h join Challenges c
on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having count(c.challenge_id) 
order by count(c.challenge_id) desc, h.hacker_id
) -- 아이디, 이름별 만든 챌린지 수를 구하고 동일한 챌린지 수에 따라 아이디 별로 넘버링
select  hacker_id
, name
, cnt
from base 
where cnt = (select max(cnt) from base ) -- 최대 챌린지 수와 동일한 경우 조회
union all
select  hacker_id
, name
, cnt
from base 
where cnt < (select max(cnt) from base ) -- 최대 챌린지 수보다 작을 경우 아이디별 첫번째만 조회
and rk =1
order by cnt desc, hacker_id

 

의도 

최대챌린지 수와 동일한 경우는 모두 조회하고

최대 챌린지 수보다 작은 경우는 1개만 출력해서

두 테이블을 union한다

 

오답 이유

문제에 대한 이해 부족

만든 챌린지의 수가 동일한 학생이 있는 경우 모두 제거해야 하는데

(대체 왜 모두 지워야 하는지 이해되지는 않지만..)

id순으로 1명씩 남겼다..

방법 : 만든 챌린지 수를 파티션으로 해서 id순으로 순위를 매겼다.

매긴 순위에서 만든 챌린지수가 50보다 작을 경우 1만 남기기

 

-- 오답 2

with base as (
    select h.hacker_id, h.name, count(c.challenge_id) as cnt
    ,row_number()over(partition by count(c.challenge_id) order by hacker_id ) rk 
from Hackers h join Challenges c
on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having count(c.challenge_id) 
order by count(c.challenge_id) desc, h.hacker_id
),
base2 as (
select  hacker_id
, name
, cnt
 ,rk
, if (cnt < (select max(cnt) from base ) and rk >1, null, cnt) as cond
from base )
select hacker_id
, name
, cnt
from base2
where cond is not null
order by cnt desc, hacker_id

의도 

if절을 추가하여 최대 챌린지 수보다 작은 경우 rk가 1보다 크면 null값으로 하여 모두 제거

결과가 오답1과 비슷

즉, rk >1일 경우 중복값이 있는 cnt가 제거 되지 못하고

rk >=1 로 할 경우 중복값이 없는 cnt보 모두 제거된다

 

하지만 중복값의 사람은 모두 제거하는게 목적 .. > row_number를 안쓰는게 좋겠다...

row_number을 조건으로 하려니까 복잡해짐

오답2(상위)	정답
하위

 

-- 정답
with base as (
    select h.hacker_id
    , h.name
    , count(c.challenge_id) as cnt
from Hackers h join Challenges c
on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
)
select  hacker_id
, name
, cnt
from base
where cnt = (select max(cnt) from base)
or 
cnt in (select cnt
from base
group by cnt
having count(*)=1)
order by cnt desc, hacker_id

 

💡SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by ->결과반환

1. WITH절 : Hackers와 Challenges를 id를 기준으로 조인하고, hacker_id,name을 그룹화하여 challenge_i수를 세서 cnt라고 별칭하라 > hacker_id,name, cnt를 조회하여 base라는 임시테이블을 만들어라
2. FROM 절: base테이블에서 
3. WHERE 절: cnt(해커가 만든 챌린지의 수)가 max(cnt)보다 큰 경우 필터링 하거나 하단 서브쿼리에 출련된 cnt에 포함될 경우 필터링 하라
   1. base테이블에서 cnt를 그룹화하여 해당 cnt의 데이터 수를 셀 때 1개인 경우cnt를 조회하여
4. ORDER BY 절: cnt의 내림차순과 hacker_id의 오름차순

* hacker_id의 오름차순이 적용되는 경우는 cnt가 50인 경우에만 반영됨

 

배운점

조건을 각각 적용해야 할 경우 or를 사용하는 것이좋다