오늘도 데이터분석

*PC화면에서 보기 적합하게 작성되었습니다. 

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.

정리
- Hackers 테이블과 Challenges 테이블을 가지고 해커 id, 이름, 학생이 한 챌린지의 총 수를 결과값으로 나타내는 쿼리 입력 (*한 학생이 여러 챌린지 수행함)
- 결과는 챌린지의 수에 따라 내림차순(정렬기준1), 시행한 챌린지 수가 같으면 해커 id(정렬기준2)로 정렬
- 단, 시행한 챌린지의 수가 같을 때 그 챌린지 수가 MAX값이 아니면 결과에서 제외

Sample Input 0

Hackers Table: 

 Challenges Table: 

Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1

For Sample Case 0, we can get the following details:

Students  5077 and 62743  both created  4 challenges, but the maximum number of challenges created is 6 so these students are excluded from the result.

풀이 

풀이순서 
- 두 테이블 조인
- hacker_id와 name으로 그룹화 
- having으로 challenge_created(각 학생이 시행한 챌린지 수) 조건문
  1) challenge_created가 MAX값일 경우 
  2) challenge_created가 중복값이 없는 경우
- 정렬기준 충족

STEP 1

두 테이블을 조인하고 해커 id, 이름, challenges_created를 결과로 나타냄

각 학생별로 id, name, 시행한 챌린지 수가 결과로 나옴

SELECT h.hacker_id, h.name, COUNT(challenge_id) challenges_created
FROM hackers h JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name

이 쿼리의 결과는 challenges_created 학생별로 시행한 챌린지 수가 같은 경우(중복)가 나옴

가장 큰 값(max)을 제외하고는 중복되는 값은 제외해줘야 함. 그래서 조건 필요

STEP 2

HAVING으로 challenges_created 조건

2-1 challenge_created가 MAX값일 경우 

SELECT MAX(challenge_created)
FROM (SELECT hacker_id,
                            COUNT(*) challenge_created
            FROM challenges
            GROUP BY hacker_id) sub

2-2 challenge_created가 중복값이 없는 경우

SELECT challenge_created
FROM (SELECT hacker_id,
                            COUNT(*) challenge_created
              FROM challenges
             GROUP BY hacker_id) sub
GROUP BY challenge_created
HAVING COUNT(*) = 1

최종 HAVING 문

각 조건을 서브쿼리로 지정

HAVING challenges_created IN (SELECT challenge_created
                                                      FROM (SELECT hacker_id,
                                                                                  COUNT(*) challenge_created
                                                                    FROM challenges
                                                                    GROUP BY hacker_id) sub
                                                      GROUP BY challenge_created
                                                      HAVING COUNT(*) = 1)
          OR challenges_created = (SELECT MAX(challenge_created)
                                                      FROM (SELECT hacker_id,
                                                                                   COUNT(*) challenge_created
                                                                    FROM challenges
                                                                    GROUP BY hacker_id) sub)

STEP 3 

정렬기준 포함한 최종 쿼리

SELECT h.hacker_id, h.name, COUNT(challenge_id) challenges_created
FROM hackers h 
     JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING  challenges_created IN (SELECT challenge_created
                               FROM (SELECT hacker_id,                                  
                                     COUNT(*) challenge_created
                                  FROM challenges
                                  GROUP BY hacker_id) sub
                            GROUP BY challenge_created
                            HAVING COUNT(*) = 1)
    OR challenges_created = (SELECT MAX(challenge_created)
                             FROM (SELECT hacker_id, 
                                   COUNT(*) challenge_created
                                    FROM challenges
                                    GROUP BY hacker_id) sub)
ORDER BY challenges_created DESC, h.hacker_id

위와 동일한 쿼리를 WITH문 사용하는 경우 

WITH counter AS(
  SELECT h.hacker_id
       , h.name
       , COUNT(*) created_challenges
  FROM hackers h
      INNER JOIN challenges c ON h.hacker_id = c.hacker_id
  GROUP BY h.hacker_id, h.name
) 

SELECT  counter.hacker_id
      , counter.name
      , counter.created_challenges
FROM counter
WHERE created_challenges = (SELECT MAX(created_challenges) 
                            FROM counter)
OR created_challenges IN (SELECT created_challenges 
                          FROM counter 
                          GROUP BY created_challenges 
                          HAVING COUNT(*) = 1) 
ORDER BY counter.created_challenges DESC, counter.hacker_id