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

with t1 as(
    select 
        uid,
        max(out_time) last_dt,
        min(in_time) first_dt
    from tb_user_log
    group by uid
),
t2 as (
    select 
    max(out_time) cur_dt,
    count(distinct uid) user_cnt
    from tb_user_log
),
t3 as(
    select 
    uid,
    user_cnt,
    timestampdiff(day, first_dt, cur_dt) first_dt_diff,
    timestampdiff(day, last_dt, cur_dt) last_dt_diff
    from t1 
    join t2 
)
select 
    user_grade,
    ROUND(count(*)/max(user_cnt), 2) ratio
from(
    select 
        uid,
        user_cnt,
        case
            when last_dt_diff <= 6 and first_dt_diff > 6 then '忠实用户'
            when first_dt_diff <= 6 then '新晋用户'
            when last_dt_diff > 6 and last_dt_diff <= 29 then '沉睡用户'
            when last_dt_diff > 29 then '流失用户'
        end as user_grade
    from t3
) a
group by user_grade
order by ratio desc