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

with temp as(
    select date(max(in_time)) first_login_time
    from tb_user_log
)
select user_grade,
       round(count(1)/(select count(distinct uid) from tb_user_log),2) ratio
from(
    select uid,
       case when datediff((select * from temp),date(max(in_time)))<7 and datediff((select * from temp),date(min(in_time)))>6 then '忠实用户' 
            when datediff((select * from temp),date(min(in_time)))<7 then '新晋用户'
            when datediff((select * from temp),date(max(in_time)))between 7 and 29 then '沉睡用户'
            when datediff((select * from temp),date(max(in_time)))>29 then '流失用户' end as user_grade
    from tb_user_log 
    group by uid
)t
group by user_grade
order by ratio desc;