[지표] 신규, 재구매, 복귀 유저 구분

[Input - sakila.rental]

[Problem]

• 유형 별 유저의 월별 매출액 계산
  • 신규: 최초 거래
  • 재구매: 직전 거래로부터 14일 이내 거래
  • 복귀: 직전 거래로부터 14일 이후 거래
  • 이탈: 마지막 거래로부터 14일 초과 상태
  • 대기: 마지막 거래로부터 14일 이내 상태

[Output]

with base as (
	select t.rental_id
		  ,t.customer_id
		  ,prev_rental
		  ,rental_date
		  ,next_rental
		  ,datediff(rental_date, prev_rental) as prev_diff
		  ,datediff(next_rental, rental_date) as next_diff
	from(
		select rental_id
			  ,customer_id
			  ,lag(rental_date, 1) over (partition by customer_id order by rental_date asc) as prev_rental
			  ,rental_date
			  ,lead(rental_date, 1) over (partition by customer_id order by rental_date asc) as next_rental
		from rental
	) t
)
select t.customer_type
	  ,round(avg(t.amount),2) as average_amount
from (
	select t1.*
		  ,case when next_diff is null 
                then case when datediff(current_date(), rental_date) > 14  then "churn"
                     else "pending"
				end
           else case when prev_diff is null then "new"
					 when prev_diff <= 14 then "retent"
			 	 	 when prev_diff > 14 then "churn_recovered"
			 		 else null end
		   end as customer_type 
		  ,t2.amount
	from base t1
	left join payment t2
	on t1.rental_id = t2.rental_id
	and t1.customer_id = t2.customer_id
) t
group by t.customer_type;