题解 | #统计活跃间隔对用户分级结果#

with today as
(select max(out_time) as t from tb_user_log),
lastday as
(select *
from 
(
select *, 
		if(min(in_time) over (partition by uid) = in_time,1,0) as new_act
from tb_user_log
) q
where (uid,in_time) in (select uid,max(in_time) 
						from tb_user_log
						group by uid)
)
select user_grade,
		round(count(user_grade) /sum(count(user_grade)) over (),2) as ratio				
from
(
select lastday.*,
         case
		 when out_time>= date_sub(today.t,interval 6 day) and out_time <= today.t  and new_act = 0 then '忠实用户' 
		 when out_time>= date_sub(today.t,interval 6 day) and out_time <= today.t  and new_act = 1 then '新晋用户' 
		 when out_time< date_sub(today.t,interval 6 day)  and out_time>= date_sub(today.t,interval 29 day) then '沉睡用户' 
		 when out_time< date_sub(today.t,interval 29 day) then '流失用户' 
		 end as user_grade
from lastday,today
) t
group by user_grade
order by ratio desc;