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

with t1 as(
select uid,date(in_time)as dt,date(max(out_time)over()) as ct   from tb_user_log
union 
select uid,date(out_time) as dt ,date(max(out_time)over()) as ct from tb_user_log
order by uid ,dt),

t2 as(
select uid,dt,ct,
TIMESTAMPDIFF(day,dt,ct) as day1 from t1),

t3 as(
select uid,case when min(day1)>=30 then '流失用户'
when min(day1)>=7 then '沉睡用户'
when max(day1)<7 then '新晋用户'
else '忠实用户' end as user_grade from t2
group by uid)



select user_grade,round(count(*)/(select count(distinct uid) from t2),2) as ratio 
from t3
group by user_grade
order by ratio desc,user_grade