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

select
    user_grade,
    round(
        count(uid) / (
            select
                count(distinct uid)
        
        from
            tb_user_log),
            2
    ) as ratio
from
    (
        select
            uid,
            (
                case
                    when min(jiange) < 7
                    and max(jiange) >= 7 then '忠实用户'
                    when max(jiange) < 7 then '新晋用户'
                    when min(jiange) >= 30 then '流失用户'
                    else '沉睡用户'
                end
            ) as user_grade
        from
            (
                select
                    uid,
                    datediff (
                        (
                            select
                                max(date (in_time))
                            from
                                tb_user_log
                        ),
                        date (in_time)
                    ) as jiange
                from
                    tb_user_log
                order by
                    uid
            ) t1
        group by
            uid
    ) t2
group by
    user_grade
order by ratio desc,user_grade