[지표] 신규, 복귀, 이탈 유저

신규, 복귀, 이탈 유저의 월별 매출액 계산

• sakila.rental
  

-- """ NEW, COMEBACK, OUT CUSTOMER """

with tidy as (
	select t2.customer_id
		  ,t2.rental_id
		  ,t2.rental_date
		  ,lag(t2.rental_date, 1) over (partition by t2.customer_id order by t2.rental_date) last_rental
		  ,datediff(t2.rental_date, lag(t2.rental_date) over (partition by t2.customer_id order by t2.rental_date)) comeback_day
		  ,lead(t2.rental_date, 1) over (partition by t2.customer_id order by t2.rental_date) next_rental
	from (
		select t1.rental_id
			  ,date_format(t1.rental_date, "%Y-%m-%d") as rental_date
			  ,t1.inventory_id
			  ,t1.customer_id
			  ,t1.return_date
			  ,t1.staff_id
			  ,t1.last_update
		from sakila.rental t1
		where 1=1
		and t1.rental_date is not null
    ) t2
),
all_tidy as (
select t1.customer_id
	  ,t1.rental_id
	  ,t2.amount
      ,date_format(t1.rental_date, '%Y%m') rental_ym
      ,t1.last_rental
      ,t1.rental_date
      ,t1.next_rental
      ,t1.comeback_day
      ,case when t1.last_rental is null then 1 else 0 end as new_customer
      ,case when t1.comeback_day between 0 and 13 then 1 else 0 end as repeat_customer
      ,case when t1.comeback_day > 14 then 1 else 0 end as comeback_customer
      ,case when datediff(next_rental, t1.rental_date) > 14 then 1 else 0 end as out_customer -- rental_date를 마지막으로 그 후에 14일 이상 접속하지 않은 유저
from tidy t1
left outer join sakila.payment t2
on t1.rental_id = t2.rental_id
)
select rental_ym
	  ,sum(case when new_customer = 1 then amount else 0 end) as new_amount
      ,sum(case when repeat_customer = 1 then amount else 0 end) as repeat_amount
      ,sum(case when comeback_customer = 1 then amount else 0 end) as comeback_amount
from all_tidy
group by rental_ym;