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

with tmp
as(
    select
    uid
    ,date(min(in_time)) first_date
    ,date(max(in_time)) last_date
    ,(select date(max(in_time)) from tb_user_log) today
    from tb_user_log
    group by uid
)

select
grade
,round(count(distinct uid) / (select count(distinct uid) from tb_user_log), 2) ratio
from
(
    select
    uid 
    ,case when datediff(today, first_date) <= 6 then '新晋用户'
        when datediff(today, first_date) > 6 and datediff(today, last_date) <= 6 then '忠实用户'
        when datediff(today, first_date) > 6 and datediff(today, last_date) >= 30 then '流失用户'
        when datediff(today, first_date) > 6 and datediff(today, last_date) > 6 then '沉睡用户'
        else '其他'
        end grade
    from tmp
)  t

group by grade