[MySQL] HackerRank - Challenges 풀이

문제 출처: https://www.hackerrank.com/challenges/challenges/problem

 

문제:

줄리아가 학생들한테 코딩 문제를 만들어 달라는 부탁을 했다. 

여기서 학생들이 각자 만든 문제를 challenges라고 칭한다.

우리는 결과로 학생들의 id(hacker_id), 이름(name), 만든 문제 수(challenges_created)를 출력할 것이다.

 

정렬 조건은 다음과 같다.

1. 문제 개수(challenges_created)는 내림차순 desc 으로 정렬한다.

   문제 개수가 같을 경우, hacker_id를 기준으로 정렬한다. 

2. 이때 

     if 중복된 문제 개수 < 최대 문제 개수,  then 중복된 문제 수를 가진 데이터는 제외한다. 

    (중복된 문제 개수 == 최대 문제 개수, 데이터를 포함한다.)

 

주어진 테이블 2개

1. Hackers

- hacker_id (int)

- name (str)

2. Challenges

- challenge_id (int)

- hacker_id (int)

 

풀이:

정렬 조건을 적용하기 전에, 먼저 학생별 만든 문제 개수를 알아보자. 

 

hacker_id를 기준으로 두 개의 테이블을 이너조인으로 합친다.

SELECT *
FROM Hackers h
     INNER JOIN Challenges c ON h.hacker_id = c.hacker_id

 

각 학생이 만든 문제 개수는

hacker_id와 name으로 GROUP BY를 적용시키고 

해당 hacker_id와 name에 상응하는 데이터의 수를 세어서 (COUNT(*))  

학생별 만든 문제 개수(challenges_created)를 알 수 있다. 

SELECT h.hacker_id 
     , h.name 
     , COUNT(*) AS challenges_created
FROM Hackers h
     INNER JOIN Challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name

 

 

이제 정렬 조건을 적용해보자.  

 

조건 1. 문제 개수(challenges_created)는 내림차순 desc 으로 정렬한다. 문제 개수가 같을 경우 hacker_id를 기준으로 정렬한다. 

SELECT h.hacker_id 
     , h.name 
     , COUNT(*) AS challenges_created
FROM Hackers h
     INNER JOIN Challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
ORDER BY challenges_created DESC, hacker_id

ORDER BY 적용하면 된다. hacker_id는 기본 정렬인 오름차순(ASC)으로 정렬 된다.  

 

 

다음 조건 2. 이때 if 중복된 문제 개수 < 최대 문제 개수,  then 중복된 문제 개수를 가진 데이터는 제외한다.

                   (if 중복된 문제 개수 == 최대 문제 개수, then 중복된 문제 개수 데이터를 포함한다.)

 

그러니까 동일한 값을 가지는 문제 개수(challenges_created)에 대해서, 

최댓값을 제외하고는 중복된 값이 없어야 한다는 말이 된다. 

 

따라서 남아있어야 할 데이터는

1. 중복되지 않은 challenges_created

2. 중복을 포함하는 challenges_created의 모든 최댓값

가 된다.

 

GROUP BY 밑에 HAVING절로 필터링을 적용해서 문제를 해결해보자. 

 

먼저

1. 중복되지 않은 challenges_created 포함

SELECT h.hacker_id 
     , h.name 
     , COUNT(*) AS challenges_created
FROM Hackers h
     INNER JOIN Challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name 
-- Keep unique challenges_created value
HAVING challenges_created IN (
                             SELECT challenges_created -- b
                             FROM( 
                                  SELECT hacker_id -- a
                                       , COUNT(*) AS challenges_created
                                  FROM Challenges
                                  GROUP BY hacker_id	                          
                                 ) sub
                            GROUP BY challenges_created
                            HAVING COUNT(*) = 1
                            )
ORDER BY challenges_created DESC, hacker_id

중복되지 않은 문제 개수를 포함하기 위해

먼저 a에서 id별로  만든 문제 개수(challenges_created)를 서브쿼리로 생성한다.

 

그다음 b에서 a에서 만든 challenges_created를 가져오는데,

가져올 때

'GROUP BY challenges_created

HAVING COUNT(*) = 1'

을 적용해서 challenges_created별 개수가 1인 데이터(= 중복 값이 없는 데이터)만 가져온다.

 

또 HAVING challenges_created 다음에 '='가 아니라 'IN'인 이유는

중복값이 없는 challenges_created가 여러개일 수 있기 때문이다. 

 

다음 조건

2. 중복을 포함하는 challenges_created의 모든 최댓값

SELECT h.hacker_id 
     , h.name 
     , COUNT(*) AS challenges_created
FROM Hackers h
     INNER JOIN Challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name 
-- Keep unique challenges_created value
HAVING challenges_created IN (
                             SELECT challenges_created
                             FROM( 
                                  SELECT hacker_id
                                       , COUNT(*) AS challenges_created
                                  FROM Challenges
                                  GROUP BY hacker_id	                          
                                 ) sub
                            GROUP BY challenges_created
                            HAVING COUNT(*) = 1
                            )
-- Keep MAX(challenges_created) value
OR challenges_created = (
                        SELECT MAX(challenges_created) -- b
                        FROM(
                             SELECT hacker_id -- a
                                  , COUNT(*) AS challenges_created
                             FROM Challenges
                             GROUP BY hacker_id
                             ) sub
                         )
ORDER BY challenges_created DESC, hacker_id

먼저 이전과 동일하게 a에서 id별 challenges_created를 서브쿼리로 생성하고,

b에서 a에서 만든 challenges_created의 최댓값을 가져온다.

 

이때 최댓값은 하나이기 때문에 OR challenges_created 다음에 '='이 온다. 

 

이렇게 문제는 해결 됐다!

문제는 해결이 됐지만 추가적으로 쿼리를 개선해보자.  

 

현재 쿼리에서는

id별 challenges_created를 계산하는 부분이 일일이 SELECT~FROM~으로 3번 정도 반복되는 단점이 있다.

 

이 부분을 WITH문을 사용해서 테이블처럼 만들어놓고 재사용 할 수 있다.

-- hacker_id, hacker_name별 만든 문제 개수(challenges_created)
WITH counter AS (
	SELECT h.hacker_id
         , h.name
         , COUNT(*) AS challenges_created
	FROM Hackers h
    	 INNER JOIN Challenges c ON h.hacker_id = c.hacker_id
	GROUP BY h.hacker_id, h.name
)

-- 조건 적용 1. 중복값이 없는 challenges_created, 2. 중복을 포함하는 모든 challenges_created 최댓값 포함
SELECT ct.hacker_id
     , ct.name
     , ct.challenges_created
FROM Counter ct
WHERE challenges_created IN (
                           SELECT challenges_created
                           FROM Counter
                           GROUP BY challenges_created
                           HAVING COUNT(*) = 1
                           )
OR challenges_created = (
                        SELECT MAX(challenges_created)
                        FROM Counter
                        )

 

테이블 처럼 쓸 수 있으니까 FROM Counter로 표현 가능하고

FROM Counter에 WHERE절로 Counter에 바로 조건을 걸면 된다.

이전보다 더 간결하고 가독성이 좋다는 것을 알 수 있다.

 

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.