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

with t1 as 
(select uid,min(date(in_time))as dt,max(date(in_time)) as dt_max
from tb_user_log 
group by uid )

select case when datediff('2021-11-04',dt) <=6 then '新晋用户'
 when datediff('2021-11-04',dt_max) <=6 then '忠实用户'
 when datediff('2021-11-04',dt_max) >=30 then '流失用户'
else  '沉睡用户' end as user_grade,
round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio
from t1 
group by user_grade
order by ratio desc

首先算出每个用户的新增日期和最近活跃日期
然后判断每个用户属于哪个类别
case when的判断是从上而下的，所以顺序可以设计一下；
对于时间差是<7还是<=7 要判断好。