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

with idList as (select uid,(case 
when timestampdiff(day,last_time,cur_date)>29 then '流失用户'
when timestampdiff(day,last_time,cur_date)>=7 then '沉睡用户'
when timestampdiff(day,register_time,cur_date) < 7 then '新晋用户'
when timestampdiff(day,last_time,cur_date) < 7 
     and timestampdiff(day,register_time,cur_date)>7 then '忠实用户'
end) as user_grade,
count(uid)over() as allnumm   
from (
select 
    uid,
    min(left(in_time,10)) as register_time,
    max(left(out_time,10)) as last_time,
    MAX(MAX(DATE(out_time)))OVER() cur_date
    from tb_user_log group by uid
) t1
) 
select user_grade,round(count(uid)/avg(allnumm),2)as ratio from idList group by user_grade 
order by ratio desc

本题的难点需要知道时间上的区间 比如近七天其实就是[T-6,T],那近30天的意思也能理解成[T-29，T],所以在判断流失用户的时候，应该判断timestamp的值大于29 而非>= 29,同理判断是不是新晋用户也应该判断是否<7 而不是<= 7