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

with
    user_active_days as (
        select distinct
            uid,
            date(in_time) as active_time
        from
            tb_user_log
        union
        select distinct
            uid,
            date(out_time) as active_time
        from
            tb_user_log
        order by
            uid,
            active_time
    ),
    boundary_day as (
        select
            max(active_time) today,
            date_sub(max(active_time), interval 7 -1 day) 7_days_before,
            date_sub(max(active_time), interval 30 -1 day) 30_days_before
        from
            user_active_days
    ),
    users_first_active_day as (
        select
            uid,
            min(active_time) as first_active_day
        from
            user_active_days
        group by
            uid
    ),
    user_activity_time_segmentation as (
        #  30_days_before， 7_days_before， today
        #  2021-10-06 ，      2021-10-29，  2021-11-04 
        # select  * from boundary_day
        select
            uid,
            active_time,
            case
                when active_time >= (
                    select
                        7_days_before
                    from
                        boundary_day
                ) then 'A' # "近7天"
                when active_time < (
                    select
                        7_days_before
                    from
                        boundary_day
                )
                and active_time >= (
                    select
                        30_days_before
                    from
                        boundary_day
                ) then 'B' # '近7~30天'
                when active_time < (
                    select
                        30_days_before
                    from
                        boundary_day
                ) then 'C' # "近30天外"
            end as date_range
        from
            user_active_days
    ),
    user_date_range_aggregation as (
        select distinct
            uid,
            GROUP_CONCAT(
                date_range
                order by
                    date_range asc SEPARATOR ","
            ) as date_range_str
        from
            user_activity_time_segmentation
        group by
            uid
    ),
    user_activity_classification as (
        select
            101,
            date_range_str,
            case
                when date_range_str = 'A' then '新晋用户'
                when date_range_str = 'C' then '流失用户'
                when date_range_str = 'B'
                or date_range_str = 'B,C' then '沉睡用户'
                else '忠实用户'
            end as user_grade
        from
            user_date_range_aggregation
    )
select
    user_grade,
    round(
        count(*) / (
            select
                count(*)
            from
                user_activity_classification
        ),
        2
    ) as ratio
from
    user_activity_classification
group by
    user_grade
order by
    ratio desc,
    user_grade