[QCC] 6회차 3번

양승우·2025년 1월 11일

코드카타

목록 보기

47/58

3번

문제

고객의 첫 주문 월을 기준으로 Cohort 그룹을 만들고,
각 Cohort 그룹에서 시간이 지남에 따라 활성 사용자 수를 계산하는 SQL 문을 작성하세요.
USER_COUNT_1_MONTH_LATER ~ USER_COUNT_12_MONTH_LATER 까지 계산해야 합니다.

각 Cohort 그룹에 대해 1개월 후부터 12개월 후까지의 활성 사용자 수를 추적합니다.

풀이 과정

데이터 확인

일단 이 문제를 풀면서 사용할 컬럼은 order_date와 customer_id 뿐이다.
다른 2개 컬럼은 제거하고 시작해도 아무 문제가 없다.

문제 이해

해당 문제는 SQL로 코호트 분석을 수행하는 문제이다
파이썬이나 태블로로는 몇 차례 코호트 분석을 연습이라도 해보았지만, sql로는 해본 적이 없어서 처음 접했을 때 삽질을 하다가 제대로 코드를 작성하지 못했다.
하지만 천천히 코호트 분석이 무엇인지, 어떤 목적으로 수행하는 지를 생각해보면 코드 작성 아이디어 자체가 아주 어렵지는 않다는 것을 알 수 있다.

코호트 분석은 아래와 같은 단계를 거친다

(1) 고객별로 '최초 주문 일자'를 구한다 (보통 단위는 월). 
(2) 동일한 최초 주문 월에 속한 고객들은 같은 코호트 그룹에 속한다.
(3) 코호트 그룹별로, 해당 고객들이 최초 주문 이후 12달 이내에 다시 주문을 한 경우를 구한다.

이를 SQL 코드 작성으로 치환하면 다음과 같다.

(1) customer_id별로 min(order_date)를 구한다. 단위를 '월'로 볼 것이기 때문에 date_format(\[날짜], '%Y-%m') 형태로 출력하면 되겠지만, '12개월 후'까지를 구현하기 위해 date_add() 함수를 사용할 것이다. 이를 위해 대신 '%Y-%m-01' 형태로 포맷팅한다. 이를 cohort_group으로 이름 붙인다.
(2) 주문별로 order_date를 cohort_group과 매칭시킬 수 있는 형태로 변환한다. 앞서 진행한 것처럼 '%Y-%m-01' 형태로 포맷팅한다.
(3) customer_id를 기준으로 INNER JOIN한다. 가장 먼저 min(order_date)를 customer_id 기준으로 진행했기 때문에, 단순 INNER JOIN 만으로도 "각 고객의 최초 주문 일자를 기준으로 나머지 주문 데이터가 결합된 형태"가 된다.
(4) cohort_group과 order_date를 매칭시킨다. 이를 통해 '최초 주문이 있었던 월의 주문 개수'를 구할 수 있다.
(5) cohort_group으로부터 1개월 뒤의 값 = order_date인 경우를 찾아, 1개월 후의 주문 수를 구한다.
(6) 이를 12개월 후까지 반복한다.

cohort_group 구하기

SELECT 
	CUSTOMER_ID 
	, date_format(min(order_date), '%Y-%m-01') AS "cohort_group"
FROM
	customer_orders	
GROUP BY
	CUSTOMER_ID
;

customer_id별로 최초 주문 일자를 구했다.
day값을 제거했기에 의도했던 대로 '연-월' 형태를 group by 기준으로 활용할 수 있게 되었다.

이 때, 포맷팅을 '%Y-%m-01'로 수행한 이후는,
문제에서 '월' 단위로 count를 요구했으며,
추후에 이 값에 date_add() 함수를 사용하고 싶기 때문이다

그냥 '%Y-%m'로만 하면 날짜가 아닌 문자열 데이터로만 인식이 되기 때문에 date_add() 함수를 적용할 수 없다.

'주문 연-월' 구하기

SELECT 
	customer_id
	, date_format(order_date, '%Y-%m-01') AS "order_date"
FROM
	customer_orders 
;

각 주문별로 날짜를 구했다. 어차피 월 단위로 묶을 것이기에 'day'값은 그냥 01로 처리해두어야 나중에 group by로 count 할 수 있다.

JOIN을 통해 count 구하기

앞서 구한 2개의 테이블을 JOIN하는 것으로 코호트 분석 결과를 얻을 수 있다.
각 고객마다 1개의 cohort_group에만 속해야 하기 때문에,
먼저 구했던 cohort_group과 주문 연-월 데이터를 customer_id를 기준으로 JOIN한다.

이후, 우리에게 필요한 건 cohort_group별 각 월마다의 count이기 때문에 case end 조건문을 통해 count를 수행한다.

1. 단 cohort_group count부터

WITH set_cohort_group AS (
	SELECT 
		CUSTOMER_ID 
		, date_format(min(order_date), '%Y-%m-01') AS "cohort_group"
	FROM
		customer_orders	
	GROUP BY
		CUSTOMER_ID
),
find_order_date AS (
	SELECT 
		customer_id
		, order_date
	FROM
		customer_orders 
	ORDER BY
		ORDER_DATE 
		, CUSTOMER_ID
)
SELECT 
	c.cohort_group
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = c.cohort_group THEN c.customer_id ELSE NULL END) AS "COHORT_USER_COUNT"
FROM
	set_cohort_group c
	INNER JOIN find_order_date o
		ON c.customer_id = o.customer_id
GROUP BY
	c.cohort_group
;

2. 제는 1개월 후를 구해보자

count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = c.cohort_group THEN c.customer_id ELSE NULL END)
앞의 값은 실제 주문 데이터를 포맷팅하는 것이니 그대로 두고, 오른쪽 c.cohort_group을 1개월씩 뒤로 미루어가면 된다.
앞서 '%Y-%m-01' 형태로 포맷팅을 해두었기에 date_add([날짜], interval 1 month)로 하면 1개월씩 날짜가 더해진다.

WITH set_cohort_group AS (
	SELECT 
		CUSTOMER_ID 
		, date_format(min(order_date), '%Y-%m-01') AS "cohort_group"
	FROM
		customer_orders	
	GROUP BY
		CUSTOMER_ID
),
find_order_date AS (
	SELECT 
		customer_id
		, order_date
	FROM
		customer_orders 
	ORDER BY
		ORDER_DATE 
		, CUSTOMER_ID
)
SELECT 
	c.cohort_group
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = c.cohort_group THEN c.customer_id ELSE NULL END) AS "COHORT_USER_COUNT"
    , count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 1 MONTH) THEN c.customer_id ELSE NULL END) AS "COHORT_USER_COUNT"

FROM
	set_cohort_group c
	INNER JOIN find_order_date o
		ON c.customer_id = o.customer_id
GROUP BY
	c.cohort_group
;

3.번부터는 노가다로 채우기

WITH set_cohort_group AS (
	SELECT 
		CUSTOMER_ID 
		, date_format(min(order_date), '%Y-%m-01') AS "cohort_group"
	FROM
		customer_orders	
	GROUP BY
		CUSTOMER_ID
),
find_order_date AS (
	SELECT 
		customer_id
		, order_date
	FROM
		customer_orders 
	ORDER BY
		ORDER_DATE 
		, CUSTOMER_ID)
SELECT 
	date_format(c.cohort_group, '%Y-%m') AS "first_order_month"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = c.cohort_group THEN c.customer_id ELSE NULL END) AS "COHORT_USER_COUNT"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 1 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_1_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 2 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_2_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 3 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_3_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 4 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_4_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 5 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_5_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 6 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_6_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 7 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_7_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 8 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_8_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 9 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_9_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 10 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_10_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 11 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_11_MONTH_LATER"
	, count(CASE WHEN date_format(o.order_date, '%Y-%m-01') = date_add(c.cohort_group, INTERVAL 12 MONTH) THEN c.customer_id ELSE NULL END) AS "USER_COUNT_12_MONTH_LATER"
FROM
	set_cohort_group c
	INNER JOIN find_order_date o
		ON c.customer_id = o.customer_id
GROUP BY
	c.cohort_group
;

코호트 분석이다보니 뒤쪽 날짜에 구매한 고객들은 '1달 뒤', '2달 뒤'의 데이터가 존재하는 것이 불가능하다. 그렇기에 계단형태 1시 방향로 0이 늘어나는 것을 볼 수 있다.