select user_grade,
round(count(user_grade)/cnt,2) ratio from (
select uid,case
when datediff(当日日期,最后活跃时间)<=6
and datediff(当日日期,第一次登录时间) >6
then '忠实用户'
when datediff(当日日期,第一次登录时间) <=6
then '新晋用户'
when datediff(当日日期,最后活跃时间)>6
and datediff(当日日期,最后活跃时间)<30
then '沉睡用户'
else '流失用户'
end user_grade,
count(uid)over() cnt
from (
select uid,
min(date(out_time)) '第一次登录时间',
(select max(date(out_time)) from tb_user_log) '当日日期',
max(date(out_time)) '最后活跃时间' from tb_user_log
group by uid) a
group by uid
) b
group by user_grade,cnt
order by ratio desc