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

# 统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。
# 用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
with t1 as
(select
    uid,
    date(min(in_time)) as mindt,
    date(max(in_time)) as maxdt
from tb_user_log
group by uid), 
t2 as
(select
    uid,
    (case  
        when (maxdt between date_sub('2021-11-04', interval 6 day) and '2021-11-04')
            and (mindt < date_sub('2021-11-04', interval 6 day)) 
        then '忠实用户'
        when mindt between date_sub('2021-11-04', interval 6 day) and '2021-11-04'
        then '新晋用户'
        when (maxdt < date_sub('2021-11-04', interval 29 day))
        then '流失用户'
        when (maxdt < date_sub('2021-11-04', interval 6 day))
        then '沉睡用户'
        end
        ) as user_grade
from t1)
select
    user_grade,
    round(count(*)/(select count(*) from t2), 2) as ratio
from t2
group by user_grade
order by ratio desc