[지표] Retention

[Input]

[Problem]

• 신규 사용자의 N일차 리텐션율(Day N Retention Rate)을 계산하라.
  즉, 사용자가 가입(첫 활동) 후 특정 일(N일) 후에도 다시 활동했는지 확인해야 한다.

[Output]

[Answer]

with base as (
	select t.user_id
		  ,datediff(t.next_event_time, t.event_time) as diff
		  ,t.idx
	from (
		select user_id
			  ,event_time
			  ,lead(event_time, 1) over (partition by user_id order by event_time asc) as next_event_time
			  ,row_number() over (partition by user_id order by event_time asc) as idx
		from funnel
	) t
	where t.idx = 1
)
select t.diff
	  ,t.cnt
      ,t.acc
      ,round(t.acc / t.total_cnt * 100, 2) as perc
from(
	select diff
		  ,count(distinct user_id) as cnt
		  ,sum(count(distinct user_id)) over (order by diff rows unbounded preceding) as acc
		  ,sum(count(distinct user_id)) over () as total_cnt
	from base
	where 1=1
	and diff is not null
	group by diff
) t
where 1=1
and diff in (1, 7, 30);