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

with a2 as(
with a1 as(
select uid,min(date(in_time)) early,max(date(in_time)) recent
from tb_user_log
group by uid)
select uid
,case when datediff('2021-11-04',early) > 6 and datediff('2021-11-04',recent) <=6 then '忠实用户'
when datediff('2021-11-04',early) <=6 and datediff('2021-11-04',recent) <=6 then '新晋用户'
when datediff('2021-11-04',recent)>6 and datediff('2021-11-04',early) <=29 then '沉睡用户' 
when datediff('2021-11-04',recent) > 29 then '流失用户' end user_grade
from a1
)
select user_grade,
round(count(user_grade)/(select count(*) from a2),2) ratio
from a2
group by user_grade
order by ratio desc;