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

 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