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

with a as (
select
    uid,
    (select date(max(out_time)) from tb_user_log) as max_time,
    date(min(in_time)) as zhuce_date, #最远一天
    date(max(in_time)) as huoyue_date  #最近一天
from
    tb_user_log
group by
    uid
),
b as (
select
    uid,
    datediff(max_time, huoyue_date) as last_diff,
    datediff(max_time, zhuce_date) as first_diff
from
    a
),
c as (
select
    uid,
    case
    when first_diff <= 6 then '新晋用户'
    when last_diff <= 6 then '忠实用户'
    when last_diff > 6 and last_diff < 30 then '沉睡用户'
    else '流失用户'
    end as user_grade
from
    b
)
select
    user_grade,
    round(count(*)/(select count(*) from c), 2) as ratio
from
    c
group by
    user_grade
order by
    ratio desc

其实只用找注册日期与现在的diff还有最近活跃日期的diff然后筛选就行了，思路是简单的，但是那个筛选条件折磨了我半个小时...