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

#思路：用每个用户最早、最晚两个活跃时间与划分标准进行判断并分类即可。
with tb1 as(
    select uid, date(in_time) dt from tb_user_log
    union all 
    select uid, date(out_time) dt from tb_user_log)

select user_grade, round(count(user_grade)/max(num),2) ratio
from (
select count(uid)over() num,
case when a<date_sub(c,interval 6 day) and b>=date_sub(c,interval 6 day) then '忠实用户'
when a>=date_sub(c,interval 6 day) then '新晋用户'
when b<date_sub(c,interval 6 day) and b>= date_sub(c,interval 29 day) then '沉睡用户'
else '流失用户' end user_grade
from (
select distinct uid, 
min(dt)over(partition by uid) a,
max(dt)over(partition by uid) b,
date('2021-11-04') c
from tb1) as tb2
) as tb3
group by user_grade
order by ratio desc, user_grade