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

with t2 as(select uid,
case when first_days<7 then '新晋用户' 
when last_days<7 then '忠实用户' 
when last_days<30 then '沉睡用户' 
else '流失用户' end user_grade
from (select uid,
datediff((select max(out_time) from tb_user_log),min(in_time)) first_days,
datediff((select max(out_time) from tb_user_log),max(out_time)) last_days
from tb_user_log 
group by uid) t1)
select t2.user_grade, round(count(distinct t2.uid)/(select count(distinct uid) from t2),2)
from t2 
group by t2.user_grade

• 使用case when 得到分类
• 利用聚合函数和datediff得到首次登录距今时间以及最近一次登录距今时间
• 注意去重