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

select
k.user_grade, round(count(k.uid)/(select count(distinct uid) from tb_user_log),2) ratio
from(
    select
    t.uid, 
    case when t.last_log_gap > 29 then '流失用户'
    when t.last_log_gap > 6 and t.last_log_gap <= 29 then '沉睡用户'
    when t.last_log_gap <= 6 and t.early_log_gap <= 6 then '新晋用户'
    else '忠实用户' end as user_grade
    from(
        select
        uid, datediff((select max(out_time) from tb_user_log), min(in_time)) early_log_gap, datediff((select max(out_time) from tb_user_log), max(out_time)) last_log_gap
        from tb_user_log
        group by uid
    ) t
) k
group by user_grade
order by ratio desc

又是参考大佬的，注意复习