不用子查询，最简洁易懂的代码

select
     case when  datediff(today,newd)<7 then '新晋用户'
     when datediff(today,dt)<7 then '忠实用户'
    when datediff(today,dt)<30 then '沉睡用户'
    else  '流失用户' end as user_grade,
    round(count(uid)/sm,2) as ratio
from (select uid,max(date(out_time)) as dt,min(date(out_time)) as newd from tb_user_log group by uid) as t1  , (select max(date(out_time)) as today,count(distinct uid) as sm from tb_user_log) as t2 
group by user_grade,today,sm
order by ratio desc

看其他大佬发的题解都复杂一点点，我这个就直接上相对简洁的代码，不做解析了