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

select 
    user_grade,
    round(count(uid)/(select count(distinct uid) from tb_user_log) ,2) as ratio
from(
    select 
        uid,
        case when rct_dt > date_sub('2021-11-04',interval 7 day) and ltr_dt < date_sub('2021-11-04',interval 7 day) then '忠实用户'
        when rct_dt > date_sub('2021-11-04',interval 7 day) and ltr_dt > date_sub('2021-11-04',interval 7 day) then '新晋用户'
        when rct_dt <= date_sub('2021-11-04',interval 30 day) and ltr_dt <= date_sub('2021-11-04',interval 30 day) then '流失用户'
        else '沉睡用户' end as user_grade
    from(
        select 
            distinct uid,
            min(date(in_time)) over (partition by uid) as ltr_dt,
            max(date(in_time)) over (partition by uid) as rct_dt
        from 
            tb_user_log
        order by uid) as a) as b
group by user_grade
order by ratio desc,user_grade