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

select  user_grade,round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio from 
(select uid,
(case when datediff((select max(date(in_time)) from tb_user_log),first_d)>6 and datediff((select max(date(in_time)) from tb_user_log),last_d)<=6 then '忠实用户'
when datediff((select max(date(in_time)) from tb_user_log),first_d)<=6 then '新晋用户'
when datediff((select max(date(in_time)) from tb_user_log),last_d)>29 then '流失用户' 
else '沉睡用户'end) as user_grade 
from
(select uid,min(date(in_time)) as first_d,max(date(in_time)) as last_d from tb_user_log
group by uid) a) b
group by user_grade
order by ratio desc