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

# 给每个用户分级
# 计算每类用户占比
# 忠实用户：[T-6, T] 至少活跃1次，且曾经也活跃过一次
# 新晋用户：[T-6, T] 至少活跃一次， 且曾经没有活跃过
# 沉睡用户：[T-6, T] 未活跃，但之前至少活跃一次
# 流失用户：[T-29, T] 未活跃，但曾经活跃过

# 只需要计算用户的最早活跃时间和最近活跃时间
select
    t3.user_grade
    , round(count(*) / (select count(distinct uid) from tb_user_log), 2) as ratio
from(
    select
        t2.uid
        , (
        case when datediff(today, latest) <= 6 and datediff(today, earliest) > 6 then "忠实用户"
            when datediff(today, earliest) <= 6 then "新晋用户"
            when datediff(today, latest) > 29 and datediff(today, earliest) > 29 then "流失用户"
            when datediff(today, latest) > 6 and datediff(today, earliest) > 6 then "沉睡用户"
            end
        ) as user_grade
    from(
        select
            t1.uid
            , min(t1.dt) as earliest
            , max(t1.dt) as latest
            , (select max(date(out_time)) from tb_user_log) as today
        from(
            select 
                ug.uid
                , date(ug.in_time) as dt
            from tb_user_log as ug
            union all
            select
                ug.uid
                , date(ug.out_time) as dt
            from tb_user_log as ug
            ) as t1
        group by t1.uid
        ) as t2
    ) as t3
group by t3.user_grade
order by ratio desc