select user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) ratio from (select uid, case when datediff('2021-11-04',max(date(in_time))) <= 6 and min(date(in_time)) < date_sub('2021-11-04',INTERVAL 6 day) then '忠实用户' when datediff('2021-11-04',min(date(in_time))) <= 6 then '新晋用户' when datediff('2021-11-04',min(date(in_time))) > 6 and datediff('2021-11-04',max(date(in_time))) < 29 then '沉睡用户' when datediff('2021-11-04',max(date(in_time))) >= 29 and datediff('2021-11-04',min(date(in_time))) >= 29 then '流失用户' else '' end as user_grade from tb_user_log group by uid) t GROUP BY user_grade order by ratio desc;