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

       when datediff(now_dt,min_dt) <= 6 then '新晋用户'
       when datediff(now_dt,max_dt) <= 6 and datediff(now_dt,min_dt) > 6 then '忠实用户'
       when datediff(now_dt,max_dt) between 7 and 29 then '沉睡用户'
       else '流失用户'
       end as user_grade
from (
select uid,(select max(date(out_time)) from tb_user_log) as now_dt,
       max(date(out_time)) max_dt,min(date(in_time)) min_dt
from tb_user_log
group by uid) a)
select user_grade,round(num/cnt,2) as ratio
from (
select user_grade,(select count(1) from t1) as cnt,count(user_grade) num
from t1
group by user_grade) b
group by user_grade
order by ratio desc