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

select 
user_grade,
round(count(uid)/max(cnt),2) as ratio
from (
select
uid,
cnt,
case when early<7 then '新晋用户'
when late>=30 then '流失用户'
when late>=7 then '沉睡用户'
else '忠实用户' end as user_grade
from(
select 
uid,
cnt,
datediff(time,min(in_time)) as early, #最早活跃
datediff(time,max(in_time)) as late #最晚活跃
from tb_user_log,(select count(distinct uid) as cnt,max(in_time) as time from tb_user_log) t2
group by 1,2
) t1
) t2 
group by 1
order by 2 desc

1. 计算总人数和当前时间 并连接上去
2. 计算每个用户最早和最晚活跃时间，从而计算据当前时间最早活跃和最晚活跃的间隔
3. 分层逻辑：
1. 最早在7天内，显然新晋
2. 最晚超过30天 显然流失
3. 最晚超过7 显然沉睡
4. 剩余都是忠实 切记7天内是相差小于等于6 如7.7和7.1 就包含了7天 但是差为6
4. 计算各分层占比