[SQL] 복잡한 서브쿼리 문제풀이(HackerRank - Challenges 문제)

🔊 본 포스팅에서 사용되는 테이블의 자료와 출처는 HackerRank 임을 밝힙니다. 더 다양한 SQL 문제를 풀어보시려면 HackerRank 사이트를 방문해 보세요!

 

HackerRank 사이트

 

이번 포스팅에서는 여러가지 서브쿼리를 이용해야 해결할 수 있는 HackerRank의 Challenges 문제를 같이 풀어보려고 한다. 우선 문제 설명과 출력 형태를 살펴보자. 문제 원본을 살펴보려면 여기를 클릭해보자. 문제 설명은 다음과 같다.

 

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.
(줄리아는 학생들에게 코딩 문제(challenges)를 만들어달라고 요청했다. 이 때 각 학생의 hacker_id, name, 문제를 만든 총 개수를 출력시켜라. 이 때 정렬은 총 문제 개수의 내림차순으로 한다. 만약 똑같은 문제 개수를 만든 학생이 2명 이상이라면, 그 때는 hacker_id 를 기준으로 오름차순으로 정렬시켜라. 또한 만약 2명 이상이 똑같은 문제 개수를 만들었을 때, 그 문제 개수가 문제 개수의 최댓값보다 작다면 그 때의 학생들은 출력시키지 않아야 한다.)

 

위 문제사항만 읽다보면 무슨 말인지 이해가 가지 않을 수 있다. 따라서 문제 원본을 살펴보면서 Input / Output 예시를 보면서 꼭 이해하고 이 포스팅을 읽어보자. 문제에서 사용된 예시 Output 형태를 살펴보자. 2가지 경우가 있다.

 

Challenges 문제의 예시 Output

 

그림 속 2 가지 예시의 차이점은 영어 설명에도 써있다 시피 challenges_created 값이 동일한 데이터가 2개 이상일 때 그 값이 challenges_created의 최댓값보다 작다면 쿼리하지 않고 동일하다면 쿼리시키면 된다.

이제 위 문제를 풀이하기 위한 사고 과정을 단계별로 나열시켜보자.

 

1. 우선 문제를 만든 개수 즉, challenges_created 값이 2개 이상 중복되지 않고 1개(unique한)인 challenges_created 값들을 알아내야 한다. 이에 대한 SQL 구문 풀이는 다음과 같다.

 

SELECT sub2.challenges_created
FROM (SELECT hacker_id, COUNT(*) AS challenges_created
      FROM Challenges
      GROUP BY Challenges.hacker_id) sub2
GROUP BY sub2.challenges_created
HAVING COUNT(*) = 1

 

우선 위 코드의 서브쿼리의 역할은 hacker_id 별로 문제를 몇 개를 만들었는지 쿼리하는 역할을 한다. 그래서 다음과 같은 형태가 나올 것이다. 하단의 테이블의 값들은 문제와 연관이 없는 값들이다. 출력 칼럼들만 동일할 뿐!

 

 

hacker_id	challenges_created
1	8
2	12
3	20
4	20
5	24

 

그리고 위 테이블 형태에서 만든 문제 개수인 challenges_created를 그룹핑으로 해서 동일한 challenges_created 값이 몇 개나왔는지 출력하고 이 때 1개인 값들만 출력해준다. 따라서 위 SQL 구문을 수행한 최종 출력 형태는 다음과 같다.(위 코드를 수행하면 하단 테이블의 COUNT(*) 칼럼은 나오지 않는다는 점 참고하자.)

 

 

challenges_created	COUNT(*)
8	1
12	1
24	1

 

 

2. 이제 challenges_created 값의 최댓값을 구해보자. 이에 대한 SQL 구문은 다음과 같다.

 

SELECT MAX(sub1.challenges_created)
FROM (SELECT COUNT(*) AS challenges_created
      FROM Hackers 
       INNER JOIN Challenges ON Hackers.hacker_id = Challenges.hacker_id
      GROUP BY Hackers.hacker_id) sub1

 

1번 단계에 비해 비교적 간단하다. 위 코드의 서브쿼리 부분은 1번 단계의 서브쿼리 구문과 하는 역할이 동일하다. 차이점은 MAX() 를 취해주었다는 것!

 

 

3. 마지막으로 위 1, 2번 조건을 충족시키는 challenges_created를 갖는 hacker_id, name을 출력시키고 challenges_created 기준으로 우선 내림차순 정렬 후 동일한 challenges_created값을 갖는 데이터들 간에는 hacker_id를 기준으로 오름차순 정렬시켜주면 된다. 전체적인 SQL 풀이 구문은 다음과 같다.

 

SELECT Hackers.hacker_id, Hackers.name, COUNT(*) AS challenges_created
FROM Hackers
 INNER JOIN Challenges ON Hackers.hacker_id = Challenges.hacker_id
GROUP BY Hackers.hacker_id, Hackers.name
HAVING challenges_created IN (SELECT sub2.challenges_created
                              FROM (SELECT hacker_id, COUNT(*) AS challenges_created
                                    FROM Challenges
                                    GROUP BY Challenges.hacker_id) sub2
                              GROUP BY sub2.challenges_created
                              HAVING COUNT(*) = 1)
    OR challenges_created = (SELECT MAX(sub1.challenges_created)
                             FROM (SELECT COUNT(*) AS challenges_created
                                   FROM Challenges
                                   GROUP BY Challenges.hacker_id) sub1)
ORDER BY challenges_created DESC, Hackers.hacker_id