[SQL풀이][StrataScratch][hard] Premium vs Freemium

 

StrataScratch - Premium vs Freemium

 

문제이해

• 일별(daily) 유료(paying) 및 무료(non_paying) 다운로드 수를 집계한 후,
• 무료 다운로드 수가 유료 다운로드 수보다 큰 레코드를 출력
• date 컬럼 기준 오름차순 정렬

 

접근방법

• 주어진 3개의 테이블 中 date가 포함된 ms_downlaod_facts를 기준으로 나머지 2개의 테이블 JOIN 실시
  • ms_download_facts : date-user별 다운로드 수
  • ms_user_dimension : user가 다운로드한 contents id
  • ms_acc_dimension : contents id별 정보(유료/무료)
• 일별 유료 다운로드 수, 무료 다운로드 수 테이블 각각 생성 후 JOIN 실시
• 무료 다운로드 수가 유료 다운로드 수 보다 높은 레코드 필터링
• date 기준 오름차순 정렬

 

풀이코드

-- 모든 테이블 결합
WITH cte AS( 
    SELECT * 
    FROM ms_download_facts AS a
    LEFT JOIN ms_user_dimension AS b
    USING (user_id)
    LEFT JOIN ms_acc_dimension AS c
    USING (acc_id)
),

-- 일별 유료 다운로드 수 집계
paying AS (
    SELECT date, SUM(downloads) AS paying
    FROM cte
    WHERE paying_customer = 'yes' -- 유료
    GROUP BY date
),

-- 일별 무료 다운로드 수 집계
non_paying AS (
    SELECT date, SUM(downloads) AS non_paying
    FROM cte
    WHERE paying_customer = 'no' -- 유료
    GROUP BY date
)

-- 유료/무료 테이블 JOIN 후 조건에 맞는 레코드 필터링 및 정렬
SELECT *
FROM non_paying AS np
LEFT JOIN paying AS p
USING (date)
WHERE non_paying > paying
ORDER BY date ASC;