JOIN이 포함된 서브쿼리

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

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.

---

Input Format

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.

---

Sample Input

Hackers Table:

Difficulty Table:

Challenges Table:

Submissions Table:

Sample Output

90411 Joe

Explanation

Hacker 86870 got a score of 30 for challenge 71055 with a difficulty level of 2, so 86870 earned a full score for this challenge.

Hacker 90411 got a score of 30 for challenge 71055 with a difficulty level of 2, so 90411 earned a full score for this challenge.

Hacker 90411 got a score of 100 for challenge 66730 with a difficulty level of 6, so 90411 earned a full score for this challenge.

Only hacker 90411 managed to earn a full score for more than one challenge, so we print the their hacker_id and name as 2 space-separated values.

 

위 문제는 둘 이상의 CHALLENGE에서 만점을 받은 해커의 HACKER_ID와 NAME을 출력하는 문제이다.  도전 횟수를 내림차순으로, 그 횟수가 같다면 HACKER_ID를 기준으로 오름차순으로 정렬해야 한다.

 

정답 

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

 

우선 서브쿼리만을 먼저 보았을 때, 결과는 다음과 같다.

SELECT S.HACKER_ID AS ID,H.NAME AS NAME, COUNT(S.HACKER_ID) AS A
FROM SUBMISSIONS AS S
INNER JOIN CHALLENGES AS C ON S.CHALLENGE_ID=C.CHALLENGE_ID
INNER JOIN DIFFICULTY AS D ON C.DIFFICULTY_LEVEL=D.DIFFICULTY_LEVEL
INNER JOIN HACKERS AS H ON S.HACKER_ID=H.HACKER_ID
WHERE S.SCORE=D.SCORE
GROUP BY S.HACKER_ID,H.NAME

10857	Kevin	6
12539	Paul	10

·

·

·

 주어진 네개의 테이블을 JOIN한 후 DIFFICULTY와 SUBMISSIONS의 SCORE가 같은, 즉 만점을 받은 해커 아이디와 이름, 총 도전 횟수가 출력된다.

 

*여기서 주의할 점은 이 문제는 SUBMISSIONS 테이블에 있는 해커들을 찾는 문제이므로 JOIN할 때 가장 먼저 두어야 한다.  그렇지 않으면 JOIN하는 과정 중 원하는 결과값이 나오지 않을 수 있다. 그리고 네 개나 되는 테이블을 JOIN하므로 헷갈릴 수 있으니 본인이 알아보기 쉽도록 AS를 이용해 이름을 지어야 한다.

 

위 테이블에서 도전 횟수가 2회 이상인 해커의 아이디와 이름을 출력하기만 하면 된다. 따라서 정답 코드는 위 정답 코드로 나오게 된다.

 

정답 코드에서 주의할 점은 서브 쿼리에서 SELECT할 열들을 AS를 이용해 필드명을 다시 지어주었는데 그 필드명을 이용하거나, 아님 서브쿼리 필드명 X를 이용해 결과 테이블을 출력해야 한다. 

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

위의 또다른 정답 코드는 서브쿼리 별칭 X를 이용해 X.HACKER_ID,X.NAME을 출력했다. 만약 X가 아닌 서브쿼리 안에 있는 테이블 명 S,C등을 이용하면 오류가 난다.