데.분.레 - 복습 15

Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기

WITH user_purchase_amount AS (
  SELECT user_id
       , SUM(amount) AS purchase_amount
    FROM action_log
    WHERE action = 'purchase'
    GROUP BY user_id
  )
, users_with_decile AS (
  SELECT user_id
       , purchase_amount
       , ntile(10) OVER(ORDER BY purchase_amount DESC) AS decile
    FROM user_purchase_amount
  )
, decile_with_purchase_amount AS (
  SELECT decile
       , SUM(purchase_amount) AS amount
       , AVG(purchase_amount) AS avg_amount
       , SUM(SUM(purchase_amount)) OVER(ORDER BY decile) AS cumulative_amount
       , SUM(SUM(purchase_amount)) OVER () AS total_amount
    FROM users_with_decile
    GROUP BY decile
  )

SELECT *
  FROM decile_with_purchase_amount

NTILE : 정해준 값을 ~~개로 나누어라

RFM 분석으로 사용자를 3가지 관점의 그룹으로 나누기

R : Recency 최근 구매일
F : Frequency 구매 횟수
M : Monetary 구매금액 합계

WITH purchase_log AS (
  SELECT user_id
       , amount
       , SUBSTRING(stamp, 1, 10) AS dt
    FROM action_log
    WHERE action = 'purchase'
  )
, user_rfm AS (
  SELECT user_id
       , MAX(dt) AS recent_date
       , CURRENT_DATE - MAX(dt::date) AS recency
       , COUNT(dt) AS frequency
       , SUM(amount) AS monetary
    FROM purchase_log
    GROUP BY user_id
  )
SELECT *
  FROM user_rfm