[SQL] 클래식 리텐션(classic retention) 분석

solvesql 플레이그라운드에서 제공하는 US E-commerce Records 2020 데이터를 사용했다.

solvesql - 플레이그라운드

solvesql - 플레이그라운드

 

-- 전처리한 TABLE은 WITH로 처리 후 저장
WITH records_preprocessed AS(
  SELECT r.customer_id
        , r.order_id
        , r.order_date
        , c.first_order_date
        , DATE_FORMAT(r.order_date, '%Y-%m-01') AS order_month
        , DATE_FORMAT(c.first_order_date, '%Y-%m-01') AS first_order_month
  FROM records AS r
      INNER JOIN customer_stats AS c ON r.customer_id = c.customer_id
      )

-- month0 : 각 월 별로 첫 구매한 고객이 몇 명인지 계산
-- month1 : 첫 주문 한 달 다음 달에도 이탈하지 않은 사용자의 수
SELECT first_order_month
      , COUNT(DISTINCT customer_id) AS month0
      , COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 1 month) = order_month THEN customer_id END) AS month1
      , COUNT(DISTINCT CASE WHEN DATE_ADD(first_order_month, INTERVAL 2 month) = order_month THEN customer_id END) AS month2
FROM records_preprocessed
GROUP BY first_order_month

SQL은 반복문은 없음..

노가다로 열 구해야함

 

< 정리할 쿼리 >

* WITH ~ AS

* CASE WHEN ~ THEN ~ END

* DATE_ADD (MYSQL문법임)