SQL | SOLVESQL 코딩테스트 : Lv3. (ANALYTICS)

Lv3. RFM 분석 3단계. 떠나간 VIP

https://solvesql.com/problems/rfm-3-left-vip/

WITH T1 AS (
  SELECT * 
    , CASE WHEN last_order_date >= '2020-12-01' THEN 'recent' ELSE 'past' END AS recency
    , CASE WHEN cnt_orders >= 3 THEN 'high' ELSE 'low' END AS frequency
    , CASE WHEN sum_sales >= 500 THEN 'high' ELSE 'low' END AS monetary
  FROM customer_stats
), T2 AS (
  SELECT recency, frequency, monetary
    , COUNT(*) AS customers
  FROM T1
  GROUP BY recency, frequency, monetary
  ORDER BY recency DESC
)

SELECT *
FROM T2
WHERE frequency = 'high' AND monetary = 'high'

❗WHERE 절에 조건 넣기

+) WITH T1

❗CASE WHEN 사용해서 RFM 칼럼 만들기

+) WITH T2

❗집계 함수 COUNT() 사용해서 집계