[Hacker Rank_MySQL]문제 풀이#Top Competitors

SQL > Basic Join > 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.

빨간 글씨는 내가 문제를 풀 때 중요하다고 생각한 부분이다.

 

SELECT H.HACKER_ID, H.NAME
FROM CHALLENGES C
JOIN HACKERS H
    ON C.HACKER_ID = H.HACKER_ID
JOIN DIFFICULTY D
    ON C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
JOIN SUBMISSIONS S
    ON C.CHALLENGE_ID = S.CHALLENGE_ID
WHERE S.SCORE = D.SCORE
GROUP BY H.HACKER_ID
HAVING COUNT(S.HACKER_ID) > 1
ORDER BY COUNT(S.HACKER_ID) DESC, S.HACKER_ID ASC

>다음과 같은 결과가 나옴

ERROR 1055 (42000) at line 4: Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'run_50ogvlk87kq.H.name'
which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

GROUP BY를 해제하고 돌려봄

SELECT H.HACKER_ID, H.NAME
FROM CHALLENGES C
JOIN HACKERS H
    ON C.HACKER_ID = H.HACKER_ID
JOIN DIFFICULTY D
    ON C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
JOIN SUBMISSIONS S
    ON C.CHALLENGE_ID = S.CHALLENGE_ID
WHERE S.SCORE = D.SCORE

HAVING COUNT(S.HACKER_ID) > 1
ORDER BY COUNT(S.HACKER_ID) DESC, S.HACKER_ID A

>다음과 같은 결과가 나옴

ERROR 1140 (42000) at line 4: In aggregated query without GROUP BY,
expression #1 of SELECT list contains nonaggregated column 'run_3hkcuidcf7e.H.hacker_id';
this is incompatible with sql_mode=only_full_group_by

다른 사람들 코드를 살펴본 결과, 테이블 조인 관계와 그룹바이에 들어갈 요소를 수정함

SELECT H.HACKER_ID, H.NAME
FROM HACKERS H
JOIN SUBMISSIONS S
    ON H.HACKER_ID = S.HACKER_ID
JOIN CHALLENGES C
    ON S.CHALLENGE_ID = C.CHALLENGE_ID
JOIN DIFFICULTY D
    ON C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
WHERE S.SCORE = D.SCORE
GROUP BY H.HACKER_ID, H.NAME
HAVING COUNT(S.HACKER_ID) > 1
ORDER BY COUNT(S.HACKER_ID) DESC, S.HACKER_ID ASC