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

with t as(
    select uid,
    max(in_time) as recent_in,
    min(in_time) as first_in,
    (select max(in_time) from tb_user_log) as today
    from tb_user_log
    group by uid
),
t1 as(
    select uid,
    case
        when datediff(today,recent_in)<=6 and datediff(today,first_in)>6 then '忠实用户'
        when datediff(today,first_in)<=6 then '新晋用户'
        # when 6<datediff(today,recent_in)<=30 then '沉睡用户'
        when  datediff(today,recent_in)>=30 then '流失用户'
        else '沉睡用户'
    end as user_grade
    from t
)
select user_grade,
round(count(user_grade)/(select count(*) from t1),2) as ratio
from t1
group by user_grade
order by ratio desc