[HackerRank] 15 Days of Learning SQL (Hard)

못 풀어서 다른사람 답안 보고 공부했다

FROM절이 거의 쓸모가 없고 네개의 각 컬럼을 다 따로 구하는 접근이 인상깊었다

해커랭크가 그렇게 어려운 플랫폼이 아닌데 이 문제만 갑자기 급발진이다 정말 어렵다

 

이런 답안을 내야 한다

컬럼1: 날짜

컬럼2: 첫날부터 지금까지 매일 최소1번 이상 제출한 사람수, 날이 갈수록 적어지는 깔대기 형태

컬럼3, 컬럼4: 이날 하루에 한해 가장 많이 제출한 사람의 아이디와 이름, 같은 횟수인 사람 있으면 id 낮은걸로

 

날짜별로 그룹바이해서 다 해결할 수 있는 문제가 아니다!

특히 컬럼2가 구하기 어렵다

 

SELECT s1.submission_date -- first column

        , (SELECT COUNT(DISTINCT s2.hacker_id) -- second column
        FROM submissions s2
        WHERE s2.submission_date = s1.submission_date
        AND (SELECT COUNT(DISTINCT s3.submission_date) 
            FROM submissions s3 
            WHERE s3.hacker_id = s2.hacker_id
            AND s3.submission_date < s1.submission_date) = DATEDIFF(s1.submission_date, '2016-03-01'))
            
     , (SELECT s2.hacker_id -- third column
        FROM submissions s2
        WHERE s2.submission_date = s1.submission_date
        GROUP BY hacker_id
        ORDER BY COUNT(submission_id) DESC, hacker_id
        LIMIT 1) AS what
        
    , (SELECT name FROM hackers WHERE hacker_id = what) -- fourth column
    
FROM (SELECT DISTINCT submission_date FROM submissions) s1
GROUP BY 1

중복제거한 고유한 날짜만 있는 테이블이 s1.

온전한 테이블이 s2와 s3.

 

컬럼2(매일매일 제출한 사람수) 구하기:

1. 날짜만 있는 s1과 사람정보가 있는 s2를 s1날짜 =  s2날짜 기준으로 조인한다.

2. 동일한 두 테이블 s2와 s3을 s2.해커id = s3.해커id 기준으로 셀프조인한다.

3. s1과 s3을 이용한 조건을 만든다. [s1보다 과거인 s3 역대 제출일 개수 = 첫날부터 s1까지의 일수 차이] 조건에 해당하는 해커를 찾으면 매일매일 한 사람을 알 수 있다. 예를 들어 s1이 3월 4일인데 역대 제출일에 3월 1일, 3월 2일, 3월 3일이 다 있으면 좌변은 3이다. 우변의 '2016-03-04' 빼기 '2016-03-01'의 결과도 3이다. 그러니까 day3까지 삼일치 제출을 다 했단거다.

4. s1날짜 = s2날짜로 되어있으니, s2의 해커id 수를 센다.

내 당장 수준에선 s3이 따로 필요하다 판단해 셀프조인할 생각을 할 순 없을 거 같다. s2와 s3가 둘다 등장하는 이 파트가 쿼리 보면서도 어려웠다. 

 

컬럼3,4(당일 하루의 최다제출자 id, 이름) 구하기:

컬럼3: 해커 기준으로 그룹바이, 제출횟수 내림차순과 아이디 오름차순으로 정렬, 첫줄만 LIMIT
컬럼4: 컬럼3의 별칭과 동일한 id일 때 이름 불러오기

이건 어렵지 않다.