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

with a as
(
    select uid,in_time,row_number() over(partition by uid order by in_time) r1,
      row_number() over(partition by uid order by in_time desc) r2,
        datediff(max(in_time) over (order by in_time desc),in_time) cha
        
    from tb_user_log
),
    b as 
(
    select uid,   
    min(case when r1=1 and cha<=6 then 0
         when r1!=1 and r2=1 and cha<=6 then 1
         when r2=1 and cha>=7 and cha<=29 then 2
         when r2=1 and cha>=30 then 3
    end) name1
    from a
    group by uid 
)

select case name1 
when 0 then '新晋用户'
when 1 then '忠实用户'
when 2 then '沉睡用户'
when 3 then '流失用户'
end ,round(count(name1)/(select count(*) from b),2)
from b
group by name1
order by 2 desc