[문제풀이] Hacker Rank - Top Competitors

출처

Top Competitors | HackerRank

 

 

문제

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

 

문제에 대한 해석

 print : hacker_id and name of hackers 
 condition : achieved full scores for more than one challenge
 sorting : descending order by the total number of challenges,ascending hacker_id

 

풀이(MYSQL)

SELECT 
    h.hacker_id
    , h.name
FROM Submissions s 
inner join Hackers h
on s.hacker_id = h.hacker_id
inner join  Challenges c
on s.challenge_id = c.challenge_id
inner join Difficulty d
on c.difficulty_level = d.difficulty_level 
where s.score = d.score
group by h.hacker_id , h.name
having count(*) > 1
order by count(*) desc, h.hacker_id

 

작동순서

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

 

1. FROM 절: Submissions, Hackers, Challenges, Difficulty를 (이너)조인 한뒤
2. WHERE 절: 제출한 점수( s.score )와 난이도별 점수(  d.score )가 같은 경우 필터링 한다
3. GROUP BY 절: 필터링 된 테이블에서 hacker_id ,name별로 그룹화 하여
4. HAVING 절: hacker_id ,name별 행의 수를 집계한 경우 2개 이상일때 필터링 하여
5. SELECT 절:  hacker_id ,name조회한다
6. ORDER BY 절: 집계된 수를 기준으로 내립차순, 같을경우 id순으로 오름차순 하여 정렬