[SQL] 월별로 잔존타입별 고객수 계산

 

아래와 같은 테이블이 있다고 가정하고, 

-- 테이블 명세 : tbl_order

customer_id : 고객 아이디 (int)

order_date : 주문일자 (date)

order_id : 주문번호 (int)

order_price : 주문금액 (int)

 

계산하려고 하는 것은 아래의 고객 타입별 고객 수입니다.

 

-- 이번달 구매한 고객 

신규고객 : 이번달 첫구매한 고객

잔존고객 : 전월과 이번달 구매한 고객 

귀환고객 : 신규고객이 아니면서, 전월 구매하지 않은 고객이 이번달 구매한 고객

 

-- 이번달 구매하지 않은 고객

이탈고객 : 전월 구매한 고객이 이번달 구매하지 않은 고객

 

 

with
first_purchase as (
  select customer_id
    , date(date_trunc(min(order_date), month)) as first_mon
  from tbl_order
  group by 1 
) 
, purchase as (
  select date(date_trunc(order_date, month)) as dt_mon
    , customer_id 
    , count(order_id) as cnt_order
  from tbl_order
  group by 1, 2 
)
, list as (
  select case when c.dt_mon is not null then c.dt_mon else date_add(p.dt_mon, interval 1 month) end as dt_mon
    , case when c.customer_id is not null then c.customer_id else p.customer_id end as customer_id
    , case when c.cnt_order >= 0 then 'y' else 'n' end as purchase_this
    , case when p.cnt_order >= 0 then 'y' else 'n' end as purchase_before
  from purchase c 
  full outer join purchase p 
    on c.provider_rowid = p.provider_rowid 
    and c.dt_mon = date_add(p.dt_mon, interval 1 month)
)
, list1 as (
  select a.dt_mon 
    , a.customer_id
    , a.purchase_this 
    , a.purchase_before 
    , b.first_mon 
  from list a
  left join first_purchase b on a.provider_rowid = b.provider_rowid 
)
select dt_mon 
  , sum(case when dt_mon = first_mon then 1 else 0 end) cnt_new
  , sum(case when dt_mon != first_mon and purchase_this = 'y' and purchase_before = 'y' then 1 else 0 end) as cnt_reside
  , sum(case when dt_mon != first_mon and purchase_this = 'y' and purchase_before = 'n' then 1 else 0 end) as cnt_return
  , sum(case when dt_mon != first_mon and purchase_this = 'n' and purchase_before = 'y' then 1 else 0 end) as cnt_churn
from list1
group by 1 
order by 1