[지표] 신규/복귀 유저 구분하기

• mst_users
  

• action_log
  

with base as
(
select u.user_id
	  ,date_format(u.register_date, '%Y-%m-%d') as r_ym
      ,date_format(a.stamp, '%Y-%m-%d') as a_ym
      ,date_format(date_sub(a.stamp, interval 1 month), '%Y-%m') as prev_a_ym 
from mst_users u
inner join action_log a 
on u.user_id = a.user_id
group by 1,2,3,4
)
select a_ym
	  ,user_id
      ,case when r_ym = a_ym
			then 'new_user'
			when lag(a_ym, 1) over (partition by user_id order by a_ym) = prev_a_ym 
			then 'repeat_user'
			else 'comeback_user'
		end as user_type
	  ,prev_a_ym
      ,r_ym
from base;