SQL 코드카타 172 - Top Competitors

https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=true

Top Competitors | HackerRank

 

Column	Type
hacker_id	Integer
name	String

 

Hackers 테이블:

• hacker_id : 테이블의 고유 키, 해커의 ID
• name : 해커의 이름

 

Column	Type
difficulty_level	Integer
score	Integer

 

Difficulty 테이블:

• difficulty_level : 챌린지의 난이도
• score : 챌린지 점수

 

Column	Type
challenge_id	Integer
hacker_id	Integer
difficulty_level	Integer

 

Challenges 테이블:

• challenge_id : 챌린지의 ID
• hacker_id : 챌린지를 만든 해커의 ID
• difficulty_level : 챌린지의 난이도

 

Column	Type
submission_id	Integer
hacker_id	Integer
challenge_id	Integer
score	Integer

 

Submissions 테이블:

• submission_id : 제출물의 ID
• hacker_id : 제출한 해커의 ID
• challenge_id : 제출물이 속한 챌린지
• score : 제출물의 점수

Julia는 코딩 대회를 막 끝냈고, 리더보드를 작성하는 데 도움이 필요합니다! 여러 챌린지에서 만점을 받은 해커의 hacker_id와 이름을 출력하는 쿼리를 작성하세요. 출력은 해커가 만점을 받은 챌린지 수를 기준으로 내림차순으로 정렬해야 합니다. 만약 여러 해커가 동일한 수의 챌린지에서 만점을 받은 경우, hacker_id를 기준으로 오름차순으로 정렬합니다.

 

select
    c.challenge_id,
    d.score max_score
from Challenges c
inner join Difficulty d on c.difficulty_level  = d.difficulty_level

 

먼저 챌린지별 배점을 출력하기 위해서 Challenges와 Difficulty를 결합한다.

 

select
    s.hacker_id,
    s.score,
    mx.max_score
from Submissions s
left join
(select
    c.challenge_id,
    d.score max_score
from Challenges c
inner join Difficulty d on c.difficulty_level  = d.difficulty_level) mx on s.challenge_id = mx.challenge_id

 

Submissions 테이블과 결합해서 제출한 문제에서 획득한 점수와 원래 그 문제의 배점을 출력한다.

 

select
    s.hacker_id,
    case
        when  s.score = mx.max_score then 1
        else 0
    end perfect_score
from Submissions s
left join
(select
    c.challenge_id,
    d.score max_score
from Challenges c
inner join Difficulty d on c.difficulty_level  = d.difficulty_level) mx on s.challenge_id = mx.challenge_id

 

새로운 열을 생성해서 제출한 문제중 만점인 문제(s.score = mx.max_score인 경우)에는 1이라고 표기한다.

 

select
    s.hacker_id,
    sum(case
        when  s.score = mx.max_score then 1
        else 0
    end) perfect_score
from Submissions s
left join
(select
    c.challenge_id,
    d.score max_score
from Challenges c
inner join Difficulty d on c.difficulty_level  = d.difficulty_level) mx on s.challenge_id = mx.challenge_id
group by s.hacker_id

 

해커의 아이디를 기준으로 그룹화한 뒤에 해당 해커가 만점을 받은 문제의 수를 센다.

 

select
    ps.hacker_id,
    ps.perfect_score
from
(select
    s.hacker_id,
    sum(case
        when  s.score = mx.max_score then 1
        else 0
    end) perfect_score
from Submissions s
left join
(select
    c.challenge_id,
    d.score max_score
from Challenges c
inner join Difficulty d on c.difficulty_level  = d.difficulty_level) mx on s.challenge_id = mx.challenge_id
group by s.hacker_id) ps
where ps.perfect_score >= 2

 

위의 쿼리를 서브쿼리로 바꿔주고 만점을 받은 문제의 수가 2개 이상인 해커들의 id만 출력하도록 한다.

 

select
    h.hacker_id,
    h.name
from
(select
    ps.hacker_id,
    ps.perfect_score
from
(select
    s.hacker_id,
    sum(case
        when  s.score = mx.max_score then 1
        else 0
    end) perfect_score
from Submissions s
left join
(select
    c.challenge_id,
    d.score max_score
from Challenges c
inner join Difficulty d on c.difficulty_level  = d.difficulty_level) mx on s.challenge_id = mx.challenge_id
group by s.hacker_id) ps
where ps.perfect_score >= 2) psh
left join Hackers h on psh.hacker_id = h.hacker_id
order by psh.perfect_score desc, h.hacker_id

 

이제 Hackers 테이블과 결합해서 해커의 id에 따라서 이름을 붙여준 후, 조건에 맞게 만점을 받은 문제의 순으로 내림차순, id에 대해서 오름차순으로 정렬하면 완료!

 

조건이 많아서 헷갈리기도 했고 코드도 길지만 어려운 문제는 아니었다.