Top Competitors / HackerRank, SQL, MySQL

문제

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.

The following tables contain contest data:
- Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
- Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score of the challenge for the difficulty level.
- Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge, and difficulty_level is the level of difficulty of the challenge.
- Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge that the submission belongs to, and score is the score of the submission. 

source: HackerRank

 

Table - Hackers

Table - Difficulty

Table - Challenges

Table - Submissions

제출답안(MySQL)

-- query a.hacker_id, b.name with more than one full score achivement ODBY # of full score achivements DESC, name
-- table: hackers(h), difficulty(d), challenges(c), submissions(s)

SELECT hacker_id, name
FROM
    (
    SELECT 
        s.hacker_id, h.name, c.challenge_id
    FROM (
        submissions AS s 
        LEFT JOIN hackers AS h ON s.hacker_id = h.hacker_id
        LEFT JOIN challenges AS c ON s.challenge_id = c.challenge_id
        LEFT JOIN difficulty AS d ON c.difficulty_level = d.difficulty_level
        )
    WHERE s.score = d.score
    )AS tb_join

GROUP BY hacker_id, name
HAVING COUNT(challenge_id)>1
ORDER BY COUNT(challenge_id) DESC, 1

 

 

 

풀이(MySQL)

1. 목표:  테이블 조인 후 hacker_id, name 출력

SELECT hacker_id, name

 

 

2-1.제출기록이 담긴 'submissions' 테이블 기준으로 다른 테이블을 조인

>> Challenge 테이블 내 hacker_id 는 challenge 를 생성한 hacker's id

>> 따라서, submissions 테이블 내 hacker_id를 기준으로 조인해야함

>> 조인해야하는 테이블이 많으므로 submissions 테이블을 기준으로 모두 LEFT JOIN

2-2. 조인 후 필요한 정보인 hacker_id, name, challenge_id를 불러오기

>> 조인 할 기준은 hacker_id >> name, challenge_id >> difficulty_level >> score (full score)

FROM
    (
    SELECT 
        s.hacker_id, h.name, c.challenge_id
    FROM (
        submissions AS s 
        LEFT JOIN hackers AS h ON s.hacker_id = h.hacker_id
        LEFT JOIN challenges AS c ON s.challenge_id = c.challenge_id
        LEFT JOIN difficulty AS d ON c.difficulty_level = d.difficulty_level
        )
    WHERE s.score = d.score
    )AS tb_join

 

3. hacker_id, name 기준으로 그룹화하고 full score challenge가 1개 초과하는 데이터만 추출하여 challenge 수 기준 내림차순, hacker_id 기준 오름차순 정렬

GROUP BY hacker_id, name
HAVING COUNT(challenge_id)>1
ORDER BY COUNT(challenge_id) DESC, 1

 

 

 

문제 바로가기(MySQL)