SQL | SOLVESQL 코딩테스트 : Lv2. (Analytics)

Lv2. RFM 분석 1단계. 고객 별 RFM 값 구해보기

https://solvesql.com/problems/rfm-1-scoring/

SELECT customer_id
  , last_order_date
  , cnt_orders
  , sum_sales
  , CASE
      WHEN last_order_date BETWEEN '2020-12-01' AND '2021-01-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

❗CASE WHEN 을 사용해서 조건에 따라 칼럼 생성

 

Lv2. RFM 분석 2단계. 고객 분류하기

https://solvesql.com/problems/rfm-2-classifying/

WITH T1 AS (
  SELECT *
    , CASE
        WHEN last_order_date BETWEEN '2020-12-01' AND '2021-01-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
)

SELECT recency, frequency, monetary, COUNT(customer_id) AS customers
FROM T1
GROUP BY recency, frequency, monetary
ORDER BY recency, frequency, monetary

❗GROUP BY 절에 recency, frequency, monetary 해서 집계

+) WITH 절

❗위 문제에서 푼 쿼리를 활용 (Lv3. RFM 분석 1단계. 고객 별 RFM 값 구해보기 참고)
❗기존 칼럼에 recency, frequency, monetary 값만 붙임