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

#第三步，统计各类用户的占比
select user_grade,round(count(uid)/sum(count(uid)) over(),2) ratio #用 SUM(COUNT(user_grade))OVER()来统计总用户数。自己开窗，不受group by user_grade限制。
from(
#第一步 做时间表为临时表，命名为time
    with time as(
    select uid,
        min(date(in_time)) f_dt,
        max(date(in_time)) l_dt,
        (select max(date(in_time)) from tb_user_log) today,
        (select date_sub(max(date(in_time)), interval 6 day) from tb_user_log) sev,
        (select date_sub(max(date(in_time)), interval 29 day) from tb_user_log) thir
    from
    tb_user_log
    group by uid)
#第二步 根据时间表用case when对用户进行分级
    select uid,
        case when f_dt >= sev then '新晋用户'
            when l_dt >= sev and f_dt < sev then '忠实用户'
            when l_dt between thir and sev then '沉睡用户'
            else '流失用户' 
            #when l_dt < thir then '流失用户'
        end as user_grade
    from time
) a
group by user_grade
order by user_grade