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

with t1 as (# 找到最近一天
    select max(date_format(out_time, '%Y-%m-%d')) as today
    from tb_user_log
),# 新晋用户（近七天新增）
t2 as (
    select uid
    from (
        select uid, min(date_format(in_time, '%Y-%m-%d')) as first_time
        from tb_user_log
        group by uid
    ) as a
    where first_time between (select date_sub(today, interval 6 day) from t1) and (select today from t1)
),# 忠实用户
t3 as (
    select uid
    from tb_user_log
    where date_format(in_time, '%Y-%m-%d') between (select date_sub(today, interval 6 day) from t1) and (select today from t1) and uid not in (select uid from t2)
),# 找到每个用户最后一次活跃时间
t4 as (
    select uid, max(date_format(out_time, '%Y-%m-%d')) as last_time
    from tb_user_log
    group by uid
)
select '忠实用户' as user_grade, round(count(DISTINCT uid) / (select count(DISTINCT uid) from tb_user_log), 2) as ratio
from t3
union all
select '新晋用户' as user_grade, round(count(uid) / (select count(DISTINCT uid) from tb_user_log), 2) as ratio
from t2
union all
(select '沉睡用户' as user_grade, round(count(uid) / (select count(DISTINCT uid) from tb_user_log), 2) as ratio
from t4
where last_time between (select date_sub(today, interval 29 day) from t1) and (select date_sub(today, interval 7 day) from t1))
union all
(select '流失用户' as user_grade, round(count(uid) / (select count(DISTINCT uid) from tb_user_log), 2) as ratio
from t4
where last_time <= (select date_sub(today, interval 30 day) from t1));