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

with t as (select 
    uid,
    (case when datediff(date((select max(in_time) from tb_user_log)),date(max(in_time)))<=6 and datediff(date((select max(in_time)from tb_user_log)),date(min(in_time)))>6 then '忠实用户'
    when datediff(date((select max(in_time) from tb_user_log)),date(min(in_time)))<=6 then '新晋用户'
    when datediff(date((select max(in_time) from tb_user_log)),date(max(in_time)))between 7 and 29 then '沉睡用户'
    when datediff(date((select max(in_time) from tb_user_log)),date(max(in_time)))>=30 then '流失用户' end) as user_grade
from tb_user_log
group by uid)

select 
    user_grade,
    round(
        count(*)/(select count(distinct uid) from tb_user_log),2
    ) as ratio
from t
group by user_grade
order by ratio desc