[solvesql] 월별 주문 리텐션 (클래식 리텐션)

with sub_t as (

select r.customer_id

, r.order_id

, r.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 left 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 date_add(first_order_month, interval 1 month) = order_month then customer_id end) as month1,

count(distinct case when date_add(first_order_month, interval 2 month) = order_month then customer_id end) as month2,

count(distinct case when date_add(first_order_month, interval 3 month) = order_month then customer_id end) as month3,

count(distinct case when date_add(first_order_month, interval 4 month) = order_month then customer_id end) as month4,

count(distinct case when date_add(first_order_month, interval 5 month) = order_month then customer_id end) as month5,

count(distinct case when date_add(first_order_month, interval 6 month) = order_month then customer_id end) as month6,

count(distinct case when date_add(first_order_month, interval 7 month) = order_month then customer_id end) as month7,

count(distinct case when date_add(first_order_month, interval 8 month) = order_month then customer_id end) as month8,

count(distinct case when date_add(first_order_month, interval 9 month) = order_month then customer_id end) as month9,

count(distinct case when date_add(first_order_month, interval 10 month) = order_month then customer_id end) as month10,

count(distinct case when date_add(first_order_month, interval 11 month) = order_month then customer_id end) as month11

from sub_t

group by 1

order by 1

 

 

처음에 order_month 를 만들지 않고, 첫주문 기준으로 count를 했더니 제대로 된 값이 출력되지 않았다. 

첫 월을 기준으로가 아니라, 한달씩 추가해가면서 해당 월의 customer_id 가 몇명일지 구하는 쿼리를 구해야 제대로 된 값이 나온다. 

sub_query 를 잘 구성해서 이용하는 것이 중요한 것 같다.