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

[Input - sakila.rental]

Problem

• 고객 별 구매횟수, 평균 구매 주기를 구하시오

[Output]

[Answer]

select t.customer_id
	  ,count(t.rental_id) as cnt_rent
      ,min(rental_date) as mn_date
      ,max(rental_date) as mx_date
      ,round(avg(datediff(t.rental_date, t.last_rental)),2) as rent_interval1
      ,round(datediff(max(t.rental_date),min(t.rental_date)) / (count(t.rental_id)-1), 2) as rent_interval2
from (
	select rental_id
		  ,customer_id
		  ,lag(rental_date, 1) over (partition by customer_id order by rental_date) as last_rental
		  ,rental_date
	from rental
) t
group by t.customer_id