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

select user_grade,round(count(user_grade)/sum(count(user_grade))over(),2) as ratio
from(
    select case when f_date<date_sub(max_date,interval 6 day) and r_date between date_sub(max_date,interval 6 day) and max_date then '忠实用户'
                when f_date between date_sub(max_date,interval 6 day) and max_date then '新晋用户'
                when r_date between date_sub(max_date,interval 29 day) and    date_sub(max_date,interval 6 day) then '沉睡用户'
                else '流失用户' end as user_grade
    from
        (select
                min(in_time) as f_date # 第一次登录日期
                ,max(in_time) as r_date # 最近一次登录日期
                ,max(max(in_time))over() as max_date # 今天 最大日期
        from tb_user_log
        group by uid
        ) t1
    )t2
group by user_grade
order by ratio desc

先取出各个用户 第一次登录日期 f_date ，最近一次登录日期 r_date 今天最大日期max_date

用户分级条件：

忠实用户：第一次登录日期 f_date < 今天最大日期max_date-6

新用户：第一登录日期 f_date 在最近7天内 用between就行了

沉睡用户： 沉睡用户的最近一次登录日期 r_date 在近7天和近30天之间，也就是[ max_date-29，max_date-6]

流失用户：else

流失用户的判断条件比较简短，可以考虑和沉睡用户换下位置，这样代码不用写那么多

小知识点 窗口函数里可以嵌套聚合函数

max(max(in_time))over() as max_date 今天最大日期

sum(count(user_grade))over() 总用户数