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

select t.user_grade,
       round(count(1)/(select count(distinct uid) from tb_user_log),2) as ratio
from
(select uid,
(case when datediff((select max(in_time) from tb_user_log),max(in_time))<7 and datediff((select max(in_time) from tb_user_log),min(in_time))>=7 then '忠实用户'
when datediff((select max(in_time) from tb_user_log),min(in_time))<7 then '新晋用户'
when datediff((select max(in_time) from tb_user_log),max(in_time)) between 7 and 29 then '沉睡用户'
when datediff((select max(in_time) from tb_user_log),max(in_time))>=30 then '流失用户' end) as user_grade
from tb_user_log
group by uid) t
group by t.user_grade
order by ratio desc