데이터리안 - SQL 데이터 분석캠프 실전반- 2주차: 클래식 리텐션 실습

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반'을 수강하며 작성한 내용입니다.

Step 1. records 테이블과 customer_stats 테이블을 연결

두 테이블을 customer_id 를 기준으로 연결하여 아래와 같은 데이터를 만들어주세요.

SELECT r.customer_id
     , r.order_id
     , r.order_date
     , c.first_order_date
FROM records r
    INNER JOIN customer_stats c ON r.customer_id = c.customer_id

Step 2. order_date, first_order_date 데이터를 ‘YYYY-MM-01’ 형태로 가공

date_format() 함수를 이용하여 order_date, first_order_date 컬럼을 ‘YYYY-MM-01’ 형태로 가공하고 각각 order_month, first_order_month로 출력해주세요.

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 r
      INNER JOIN customer_stats c ON r.customer_id = c.customer_id
  )

SELECT first_order_month
     , COUNT(DISTINCT customer_id) AS month0
FROM records_preprocessed
GROUP BY first_order_month

Step 3. 위 데이터를 사용하여 각 월별 첫 구매 고객 수 계산

SQ
각 월별로 첫 구매한 고객 수가 몇 명인지 계산해 month0으로 출력해주세요.

SELECT CASE WHEN c.first_order_date THEN DATE_FORMAT(c.first_order_date, '%Y-%m-01') END AS first_order_month
     , COUNT(DISTINCT r.customer_id) AS month0
FROM records r
    INNER JOIN customer_stats c ON r.customer_id = c.customer_id
GROUP BY first_order_month

Step 4. 첫 구매한 달의 다음 달에도 구매한 고객 수 계산

첫 구매한 다음 달에도 구매한 고객 수가 몇 명인지 계산해 month1로 출력해주세요.

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 r
      INNER JOIN customer_stats c ON r.customer_id = c.customer_id
  )

SELECT first_order_month
     , COUNT(DISTINCT customer_id) AS month0
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 1 MONTH) THEN customer_id END) AS month1
FROM records_preprocessed
GROUP BY first_order_month, 'first_order_month + 1 month'
ORDER BY first_order_month

Step 5. 첫 구매한 달로부터 2~11개월 뒤에도 주문한 고객 수 계산

첫 구매한 2개월 뒤, 3개월 뒤, 4개월 뒤, … 11개월 뒤에도 구매한 고객 수가 몇 명인지 계산해 month2, month3, month4... month11로 출력해주세요.

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 r
      INNER JOIN customer_stats c ON r.customer_id = c.customer_id
  )

SELECT first_order_month
     , COUNT(DISTINCT customer_id) AS month0
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 1 MONTH) THEN customer_id END) AS month1
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 2 MONTH) THEN customer_id END) AS month2
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 3 MONTH) THEN customer_id END) AS month3
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 4 MONTH) THEN customer_id END) AS month4
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 5 MONTH) THEN customer_id END) AS month5
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 6 MONTH) THEN customer_id END) AS month6
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 7 MONTH) THEN customer_id END) AS month7
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 8 MONTH) THEN customer_id END) AS month8
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 9 MONTH) THEN customer_id END) AS month9
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 10 MONTH) THEN customer_id END) AS month10
     , COUNT(DISTINCT CASE WHEN order_month = DATE_ADD(first_order_month, INTERVAL 11 MONTH) THEN customer_id END) AS month11
FROM records_preprocessed
GROUP BY first_order_month, 'first_order_month + 1 month'
ORDER BY first_order_month