코테준비 | 15 Days of Learning SQL (+해커랭크 고 난이도, where 절 연관 서브쿼리 활용)

 

15 Days of Learning SQL | HackerRank

☑️  15 Days of Learning SQL

[문제]

• Julia conducted a  days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.
• Write a query to print total number of unique hackers who made at least  submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.
• 출력 컬럼
  1. 날짜별 테스트 기간동안(2016-03-01~2016-03-16) 하루에 적어도 1번 이상 제출한 토탈 해커 수(고유) 집계
  2. 날짜별 가장 많이 제출한 해커id 와 이름 출력 ( 제출수 동률시 hacker_id 오름차순으로 출력)
• 정렬 조건 
  •  날짜 오름차순

---

[문제 풀이]

내 코드 ( 오답 처리) 

SELECT s.submission_date,t.total_hackers,b.hacker_id,h.name
FROM submissions s
    INNER JOIN ( -- best_hacker table (named 'b')
            SELECT submission_date, hacker_id
            FROM submissions s1
            WHERE hacker_id = ( 
                                SELECT hacker_id
                                FROM submissions s2
                                WHERE s1.submission_date = s2.submission_date
                                GROUP BY hacker_id
                                ORDER BY COUNT(DISTINCT submission_id) DESC,hacker_id
                                LIMIT 1)
            GROUP BY submission_date, hacker_id
            ) b ON s.submission_date = b.submission_date
    INNER JOIN hackers h ON b.hacker_id = h.hacker_id
    INNER JOIN ( -- 'total_hacker_cnts' table (named 't')
            SELECT submission_date
                   ,COUNT(DISTINCT hacker_id) AS total_hackers
            FROM submissions s3
            WHERE hacker_id IN (
                                SELECT hacker_id
                                FROM Submissions
                                WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
                                GROUP BY hacker_id
                                HAVING COUNT(DISTINCT submission_date) = 15 )
            GROUP BY submission_date
            ) t ON s.submission_date = t.submission_date
GROUP BY s.submission_date,t.total_hackers,b.hacker_id,h.name
ORDER BY 1

 

수정 코드 (정답)

SELECT s.submission_date,t.total_hackers,b.hacker_id,h.name
FROM submissions s
    INNER JOIN ( -- best_hacker table (named 'b')
            SELECT submission_date, hacker_id
            FROM submissions s1
            WHERE hacker_id = ( 
                                SELECT hacker_id
                                FROM submissions s2
                                WHERE s1.submission_date = s2.submission_date
                                GROUP BY hacker_id
                                ORDER BY COUNT(DISTINCT submission_id) DESC,hacker_id
                                LIMIT 1)
            GROUP BY submission_date, hacker_id
            ) b ON s.submission_date = b.submission_date
    INNER JOIN hackers h ON b.hacker_id = h.hacker_id
    INNER JOIN ( -- 'total_hacker_cnts' table (named 't')
            SELECT submission_date
                   ,COUNT(DISTINCT hacker_id) AS total_hackers
            FROM submissions s3
            WHERE hacker_id IN (
                                SELECT hacker_id
                                FROM Submissions s4
                                WHERE s4.submission_date <= s3.submission_date
                                GROUP BY hacker_id
                                HAVING COUNT(DISTINCT s4.submission_date)= DATEDIFF(s3.submission_date,'2016-03-01')+1)
            GROUP BY submission_date
            ) t ON s.submission_date = t.submission_date
GROUP BY s.submission_date,t.total_hackers,b.hacker_id,h.name
ORDER BY 1

 

이슈 및 해결 과정

• 해커랭크 문제 중 가장 시간이 오래 걸렸고, 튜터님 질의를 통해 해결 → 일주일 후 다시 풀어볼 필요가 있겠음
• 풀이 전 특이사항
  • 해커랭크 mysql 버전은 5.7.27 → CTE /window functions (row_number 등 X) 지원 안함
  • 즉, subquery & join 으로만 문제를 해결해야 함
• 풀이 아이디어
  • 날짜별 총 해커수, 날짜별 최대 제출 수 해커 id, name 각각 서브쿼리로 집계 한 후 최종 아우터 쿼리랑 join 하기
• 단계별 풀이
  1. join 1 &2  ) 날짜별 가장 많이 제출한 해커 id &그 name 연결
  2. join 3 ) 날짜별 총 해커 수 (테스트 기간 동안 하루도 빠짐없이 적어도 1번 이상 제출한 해커 만 집계)
  3. 각 서브쿼리 inner join 으로 연결 
  4. 아우터 쿼리에서 최종 출력컬럼으로 group by (중복 제거 목적) & 정렬 조건 확인 

-- # 가장 많이 제출한 해커 id 집계 테이블

SELECT submission_date, hacker_id
FROM submissions s1
WHERE hacker_id = ( -- 해커 아이디 아래 조건으로 필터링 
					-- 아우터 쿼리의 날짜별, 해커아이디당 고유 제출수가 가장 많고,해커아이디가 가장 작은 해커아이디 1개 가져옴
                    SELECT hacker_id
                    FROM submissions s2
                    WHERE s1.submission_date = s2.submission_date -- 연관 서브쿼리/아우터쿼리로 날짜 가져오기
                    GROUP BY hacker_id
                    ORDER BY COUNT(DISTINCT submission_id) DESC,hacker_id
                    LIMIT 1)
GROUP BY submission_date, hacker_id -- 날짜별 해커 아이디 그룹핑 (중복제거)

-- # 테스트기간 각 일자별 적어도 1개 이상은 제출한 총 해커 수 집계 테이블    

SELECT submission_date
       ,COUNT(DISTINCT hacker_id) AS total_hackers -- 날짜별 해커 수 집계
FROM submissions s3
WHERE hacker_id IN ( -- 해커 아이디 아래 조건으로 필터링
                    SELECT hacker_id
                    FROM Submissions s4
                    WHERE s4.submission_date <= s3.submission_date -- 아우터 쿼리 날짜별, 최초시작일(2016-03-01) 부터 해당날짜까지 가져옴 
                    GROUP BY hacker_id
                    HAVING COUNT(DISTINCT s4.submission_date)= DATEDIFF(s3.submission_date,'2016-03-01')+1) -- 해커 아이디 기준, 고유 제출일 수가 해당날짜까지의 기간과 동일한 것만 필터링
GROUP BY submission_date -- 날짜 기준 (중복제거)

• 날짜별 총 해커 수 집계 파트 아이디어
  • 해당 서브쿼리를 다중행 서브쿼리로 해커 아이디 필터링 진행
  • 쿼리 설명 : 0301~0315 일 동안 해커아이디 기준 제출일자가 15개가 되는 해커 아이디 모두 필터링 
• 오류 
  • 첫날~ 마지막날 까지 적어도1개이상 제출한 해커 아이디가 한번에 집계 되어 출력됨 
  • 아래 결과값과 같이 총 해커수 '35' 로 고정 되어 최종 출력
  • where 절 서브쿼리 문제
• 해결
  • 문제의 요구사항은 각 날짜별 적어도 1개 이상 제출한 해커아이디만 출력
  • where 절 연관 서브쿼리로 누적 날짜 연결 : `WHERE s4.submission_date <= s3.submission_date`
    • ex. 03/03 -> 서브쿼리( 03/01~03/03 날짜 연결) 
  • 해커 아이디 기준, 고유 제출일 수가 해당 날짜까지의 기간과 동일한 것만 필터링
    • `HAVING COUNT(DISTINCT s4.submission_date)= DATEDIFF(s3.submission_date,'2016-03-01')+1)`
    • ex) 03/03 의 해커 아이디별 고유 제출 일 수 3회 =  03/01~03/03 해당 기간 3일 

# 오답 
SELECT submission_date
       ,COUNT(DISTINCT hacker_id) AS total_hackers
FROM submissions s3
WHERE hacker_id IN (
                    SELECT hacker_id
                    FROM Submissions
                    WHERE submission_date BETWEEN '2016-03-01' AND '2016-03-15'
                    GROUP BY hacker_id
                    HAVING COUNT(DISTINCT submission_date) = 15 )
GROUP BY submission_date

# 정답
 SELECT submission_date
       ,COUNT(DISTINCT hacker_id) AS total_hackers
FROM submissions s3
WHERE hacker_id IN (
                    SELECT hacker_id
                    FROM Submissions s4
                    WHERE s4.submission_date <= s3.submission_date
                    GROUP BY hacker_id
                    HAVING COUNT(DISTINCT s4.submission_date)= DATEDIFF(s3.submission_date,'2016-03-01')+1)
GROUP BY submission_date

 

 

 

한줄 포인트

• 연관 서브쿼리 적용시 아우터 쿼리 1 행 기준으로(샘플) 먼저 어떻게 연결 되는지 생각해보면 쉽게 접근할 수 있음.