HackerRank: Challenges - WITH 구문

HackerRank: Challenges

HackerRank 의 SQL 문제이다. 서브쿼리를 활용하는 문제인데, WITH 구문을 활용하면 좀 더 쉽게 풀 수 있다.

원본 링크: Challenges | HackerRank

Challenges | HackerRank

 

문제

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.

 

문제 해설

1. hacker_id, name, 학생별 challenge 개수를 출력
2. 학생별 challenge 개수 내림차순으로 정렬(DESC), hacker_id 오름차순 정렬(ASC)
3. 학생별 challenge 개수가 같은 경우, 학생별 challenge 개수의 최댓값과 같으면 출력, 그렇지 않으면 출력에서 제외

 

예시

  1. 학생별 challenge 개수 중복값 발생 - 그 값이 최댓값인 경우, 출력O

12299 Rose 6
34856 Angela 6
79345 Frank 4
80491 Patrick 3
81041 Lisa 1

* Rose, Angela는 값이 6으로 같으나, 최댓값이므로 출력에 포함된다.

 

  2. 학생별 challenge 개수 중복값 발생 - 그 값이 최댓값이 아닌 경우, 출력X

21283 Angela 6
88255 Patrick 5
5077 Rose 4
62743 Frank 4
96196 Lisa 1

* 값이 4로 같은 Rose, Frank는 출력에서 제외된다. 결과적으로 Angela, Patrick, Lisa 만 출력된다. 

 

풀이

1. WITH 구문 작성

WITH 절은 이름을 가진 서브쿼리를 정의하는 구문이다. 재사용이 가능하므로 반복적으로 같은 SQL 구문을 작성해야 할 경우 유용하다. 위의 문제에서는 hackers, challenges 두 테이블을 조인해야 하고, hacker_id 별 challenge의 개수를 세는 것이 조건이므로 JOIN, GROUP BY 절을 서브쿼리마다 매번 반복해서 써야 한다. 그러므로 WITH 절을 이용하는 것이 효과적이다.

-- 가상 테이블 선언
WITH challenge_counts AS (
    SELECT h.hacker_id as hacker_id, h.name as name, COUNT(challenge_id) as c_cnt
    FROM hackers h JOIN challenges c ON (h.hacker_id = c.hacker_id)
    GROUP BY hacker_id, name
)

• hackers 와 challenges 테이블을 'hacker_id' 컬럼을 기준으로 조인
• 학생별 challenge 개수를 새야하므로, hacker_id를 기준으로 그룹을 나누고, challenge_id 를 count
  • GROUP BY 절 사용 시 모든 컬럼은 GROUP BY 절에 사용되거나, 집계함수를 이용해야 한다. 따라서 name 컬럼이 그룹핑에 사용되지 않더라도 GROUP BY 절에 써준다.
• WITH  구문 - WITH [테이블명] AS (서브쿼리): WITH 구문으로 challenge_counts 라는 가상테이블을 선언한다. AS 뒤에 오는 서브쿼리를 challenge_counts라는 이름으로 재사용할 수 있다. 어디까지나 '서브쿼리'의 영역이므로, with절 다음에 세미콜론(;)을 삽입하면 안된다. view가 아닌 서브쿼리이기 때문에, with 구문을 사용할 select 구문까지 모두 쓰고 나서 문장을 종료해야 한다. 

2. SELECT 구문 작성

WITH 절에서 선언한 서브쿼리를 이용하여 문제의 조건 - (정렬 및 출력) 을 만족하는 SELECT 절을 작성한다.

SELECT hacker_id, name, c_cnt
FROM challenge_counts
WHERE c_cnt IN (SELECT max(c_cnt) FROM challenge_counts) -- WHERE 조건 1
    OR c_cnt NOT IN (SELECT c_cnt FROM challenge_counts GROUP BY c_cnt HAVING COUNT(*) > 1) -- WHERE 조건 2
ORDER BY c_cnt DESC, hacker_id;

• hacker_id, name, 학생별 challenge 개수(c_cnt)를 출력: WITH 절에서 선언한 서브쿼리의 컬럼명을 그대로 사용한다.
• FROM: WITH 절에서 선언한 서브쿼리(challenge_counts)를 사용. 
• WHERE 조건
  1. 학생별 challenge 개수가 그 최댓값과 같다면 출력: c_cnt IN (최댓값 조건)
  2. 학생별 challenge 개수가 같은 경우, 최댓값이 아니라면 모두 출력에서 제외: c_cnt NOT IN (중복 조건)
• ORDER BY: 학생별 challenge 개수 별 내림차순(DESC), hacker_id 오름차순

 

전체 코드

-- 서브쿼리 선언
WITH challenge_counts AS (
    SELECT h.hacker_id as hacker_id, h.name as name, COUNT(challenge_id) as c_cnt
    FROM hackers h JOIN challenges c ON (h.hacker_id = c.hacker_id)
    GROUP BY hacker_id, name
) -- 세미콜론 X
-- 출력조건 작성
SELECT hacker_id, name, c_cnt
FROM challenge_counts
WHERE c_cnt IN (SELECT max(c_cnt) FROM challenge_counts) -- WHERE 조건 1
    OR c_cnt NOT IN (SELECT c_cnt FROM challenge_counts GROUP BY c_cnt HAVING COUNT(*) > 1) -- WHERE 조건 2
ORDER BY c_cnt DESC, hacker_id;