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

with zb3 as (with zb2 as (with zb as (select uid,out_time,max(out_time) over() as max_time,/*离现在最近时间*/
first_value(out_time) over(partition by uid order by out_time desc) near_time,/*用户最近登录时间*/
first_value(out_time) over(partition by uid order by out_time) first_time /*用户第一次登录时间*/
from tb_user_log)
select *,datediff(max_time,near_time) diff_time /*最近时间和最近登录时间差*/
from zb
where out_time = near_time) /*去重,只留下最近登录时间,其他时间没有计算意义*/
select uid,(case when out_time=first_time and diff_time <=6 then '新晋用户' /*当最近登录时间和第一次登录时间一样,该用户则为新用户*/
			when diff_time <=6 and out_time!=first_time then '忠实用户'
            when diff_time <=29 then '沉睡用户' 
            else '流失用户' end) user_grade,count(*) over() sum_p 
from zb2)
select user_grade,round(count(uid)/max(sum_p),2) ratio
from zb3
group by user_grade
order by ratio desc,user_grade