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

SELECT t.grade, round( count(t.uid)/(select count(distinct uid) from tb_user_log),2) as cnt FROM( select uid, case when datediff(date((select max(in_time) from tb_user_log)),date(max(in_time)))<=6 and datediff(date((select max(in_time) from tb_user_log)),date(min(in_time)))>6 then "忠实用户" when datediff(date((select max(in_time) from tb_user_log)),date(min(in_time)))<=6 then"新晋用户" when datediff(date((select max(in_time) from tb_user_log)),date(max(in_time))) between 7 and 29 then "沉睡用户" when datediff(date((select max(in_time) from tb_user_log)),date(max(in_time)))>29 then "流失用户" end as grade from tb_user_log group by uid ) as t group by t.grade order by cnt desc

与大佬思路不同的是，我是按照最晚的登录时间当成NOW，每个用户登录的最晚的时间7-29天作为沉睡用户，>29天为流失用户，这样好理解