Challenges

태그

서브쿼리

링크

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

한 번 더 체크

문제

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

•

Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

•

Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

예시

Sample Input 0

Hackers Table:

Challenges Table:

Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1
Plain Text
복사

Sample Input 1

Hackers Table:

Challenges Table:

Sample Output 1

Rose 6
Angela 6
Frank 4
Patrick 3
Lisa 1
Plain Text
복사

정답

(SELECT MIN(H.hacker_id) id, MIN(H.name) name, COUNT(C.challenge_id) c_count
FROM Challenges C LEFT JOIN Hackers H
ON C.hacker_id = H.hacker_id
GROUP BY H.hacker_id
HAVING COUNT(C.challenge_id) = (select COUNT(C.challenge_id)
                                FROM Challenges C LEFT JOIN Hackers H
                                ON C.hacker_id = H.hacker_id
                                GROUP BY H.hacker_id
                                ORDER BY COUNT(C.challenge_id) DESC
                                LIMIT 1)
)
UNION
(SELECT MIN(id), MIN(name), MIN(c_count)
FROM(
SELECT MIN(H.hacker_id) id, MIN(H.name) name, COUNT(C.challenge_id) c_count
FROM Challenges C LEFT JOIN Hackers H
ON C.hacker_id = H.hacker_id
GROUP BY H.hacker_id
ORDER BY c_count DESC, id ASC
) TEMP
GROUP BY TEMP.c_count
HAVING COUNT(*) < 2
)
ORDER BY c_count DESC, id
;
SQL
복사

SELECT MIN(C.hacker_id), MIN(H.name), COUNT(c.hacker_id) c_count
FROM Hackers H INNER JOIN Challenges C ON C.hacker_id = H.hacker_id
GROUP BY C.hacker_id
Having c_count = (SELECT MAX(temp1.cnt)
                  FROM (SELECT COUNT(hacker_id) as cnt
                        FROM Challenges
                        GROUP BY hacker_id
                        ORDER BY hacker_id) temp1)
        OR c_count IN (SELECT t.cnt
                       FROM (SELECT COUNT(*) as cnt 
                             FROM challenges
                             GROUP BY hacker_id) t
                       GROUP BY t.cnt
                       HAVING COUNT(t.cnt) = 1)
ORDER BY c_count DESC, C.hacker_id
;
SQL
복사

풀이

•

UNION과 서브쿼리를 이용하여 문제를 풀었다.

•

우선 hackers 테이블에서 hackers_id 별로 그룹핑을 한 뒤 가장 많이 맞춘 횟수를 LIMIT을 통해 구하였다.

(select COUNT(C.challenge_id)
 FROM Challenges C LEFT JOIN Hackers H
 ON C.hacker_id = H.hacker_id
 GROUP BY H.hacker_id
 ORDER BY COUNT(C.challenge_id) DESC
 LIMIT 1)
SQL
복사

•

위의 쿼리를 HAVING 절의 서브쿼리로 활용해서 hacker_id로 그룹핑 한 뒤 최대 횟수를 채운 데이터는 모두 출력하게끔 하였다.

(SELECT MIN(H.hacker_id) id, MIN(H.name) name, COUNT(C.challenge_id) c_count
FROM Challenges C LEFT JOIN Hackers H
ON C.hacker_id = H.hacker_id
GROUP BY H.hacker_id
HAVING COUNT(C.challenge_id) = (select COUNT(C.challenge_id)
                                FROM Challenges C LEFT JOIN Hackers H
                                ON C.hacker_id = H.hacker_id
                                GROUP BY H.hacker_id
                                ORDER BY COUNT(C.challenge_id) DESC
                                LIMIT 1)
)
SQL
복사

•

그 다음 UNION할 쿼리에서 challenges와 hackers를 JOIN한 테이블을 hacker_id로 그룹핑하여 맞춘 횟수를 count한 데이터를 인라인 뷰로 사용하였다.

FROM(
SELECT MIN(H.hacker_id) id, MIN(H.name) name, COUNT(C.challenge_id) c_count
FROM Challenges C LEFT JOIN Hackers H
ON C.hacker_id = H.hacker_id
GROUP BY H.hacker_id
ORDER BY c_count DESC, id ASC
) TEMP
SQL
복사

•

그 다음 다시 c_count를 기준으로 그룹핑을 해서 count가 2 미만인 데이터만 출력하게끔 하였다.

(SELECT MIN(id), MIN(name), MIN(c_count)
FROM(
SELECT MIN(H.hacker_id) id, MIN(H.name) name, COUNT(C.challenge_id) c_count
FROM Challenges C LEFT JOIN Hackers H
ON C.hacker_id = H.hacker_id
GROUP BY H.hacker_id
ORDER BY c_count DESC, id ASC
) TEMP
GROUP BY TEMP.c_count
HAVING COUNT(*) < 2
)
SQL
복사

•

두 메인쿼리를 UNION으로 묶은 뒤 마지막에 문제 조건에 따라 정렬을 해주었다.

ORDER BY c_count DESC, id
SQL
복사

•

HAVING 절에 서브쿼리를 활용하여 문제를 풀었다.

•

조건은 두 개이다.

최대 횟수를 만족한 데이터이거나

맞춘 횟수가 다른 데이터와 겹치지 않거나

최대 횟수를 만족한 데이터를 찾기 위한 서브쿼리

Having c_count = (SELECT MAX(temp1.cnt)
                  FROM (SELECT COUNT(hacker_id) as cnt
                        FROM Challenges
                        GROUP BY hacker_id
                        ORDER BY hacker_id) temp1)
SQL
복사

맞춘 횟수가 다른 데이터와 겹치지 않는 데이터를 찾기 위한 서브쿼리

OR c_count IN (SELECT t.cnt
               FROM (SELECT COUNT(*) as cnt 
                     FROM challenges
                     GROUP BY hacker_id) t
               GROUP BY t.cnt
               HAVING COUNT(t.cnt) = 1)
SQL
복사