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

with tb_user_grade as (
    select
        uid,
        case
            when min(date(in_time)) between date_sub('2021-11-04',interval 6 day) and '2021-11-04' then '新晋用户'
            when max(date(in_time)) between date_sub('2021-11-04',interval 29 day) and date_sub('2021-11-04',interval 7 day) then '沉睡用户'
            when (max(date(in_time)) between date_sub('2021-11-04',interval 6 day) and '2021-11-04') and min(date(in_time)) < date_sub('2021-11-04',interval 6 day) then '忠实用户'
            else '流失用户'
        end as user_grade
    from 
        tb_user_log
    group by 
        uid
)

select
    user_grade,
    round(count(*) / (select count(*) from tb_user_grade),2) as ratio
from 
    tb_user_grade
group by 
    user_grade
order by 
    ratio desc