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

-- 分级 活跃间隔 各活跃等级用户占比 
with t1 as (select count(distinct case when datediff(current_time1,max_time) <= 6 and datediff(current_time1,min_time) > 6 then uid else null end) zhongshi_label,
count(distinct case when datediff(current_time1,max_time) <=6 and datediff(current_time1,min_time) <= 6 then uid else null end) xinji_label,
count(distinct case when datediff(current_time1,max_time) >6 and datediff(current_time1,min_time) > 6 then uid else null end) chenshui_label,
count(distinct case when datediff(current_time1,max_time) >29 and datediff(current_time1,min_time) > 29 then uid else null end) liushi_label
from (select uid,
max(in_time) over () current_time1,
min(in_time) over (partition by uid) min_time,
max(in_time) over (partition by uid) max_time,
in_time,out_time
from tb_user_log) t0)

select '忠实用户' user_grade,round(zhongshi_label/(zhongshi_label+xinji_label+chenshui_label),2) ratio from t1
union all 
select '新晋用户' user_grade,round(xinji_label/(zhongshi_label+xinji_label+chenshui_label),2) ratio from t1
union all 
select '沉睡用户' user_grade,round((chenshui_label - liushi_label)/(zhongshi_label+xinji_label+chenshui_label),2) ratio from t1
union all 
select '流失用户' user_grade,round(liushi_label/(zhongshi_label+xinji_label+chenshui_label),2) ratio from t1