[지표] 재구매여부, 구매주기

• sakila.rental
  

-- """ 재구매율 / 구매주기 """
-- 재구매자 : 최초 구매일 이후, +1일 후 구매자 
-- 구매주기 : 구매간격(최근구매일자 - 최초구매일자) / (구매횟수-1)

with tb_all as (
	select t.customer_id
		  ,t.rental_id
		  ,t.priv_rental_date
		  ,t.rental_date
		  ,t.next_rental_date
		  ,datediff(rental_date, prev_rental_date) as comeback_dur
		  ,case when priv_rental_date is null then 1 else 0 end as new_customer
	from (
		select customer_id
			  ,rental_id
			  ,lag(rental_date) over (partition by customer_id order by rental_date asc) priv_rental_date
			  ,rental_date
			  ,lead(rental_date) over (partition by customer_id order by rental_date asc) next_rental_date
		from sakila.rental 
	) t
)
select customer_id
	  ,min(rental_date) as first_rental_최초구매일
      ,max(rental_date) as last_rental_최근구매일
      ,count(rental_id) rental_cnt_구매횟수
      ,case when date_add(min(rental_date), interval 1 day) <= max(rental_date) then 1 else 0 end as comeback_yn_재구매여부
      ,datediff(max(rental_date), min(rental_date)) as all_days_구매간격
      ,case when count(rental_id)-1 = 0 or datediff(max(rental_date), min(rental_date)) = 0 then 0 
            else round(datediff(max(rental_date), min(rental_date)) / (count(rental_id)-1),1) end as interval_days_구매주기
from tb_all
group by customer_id;