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

select
    user_grade,
    round(num / sum(num) over (), 2) as ratio
from
    (
        select
            user_grade,
            count(distinct uid) as num
        from
            (
                select
                    uid,
                    case
                        when first_log < 7 then '新晋用户'
                        when last_log < 7 then '忠实用户'
                        when last_log < 30 then '沉睡用户'
                        else '流失用户'
                    end user_grade
                from
                    (
                        select
                            uid,
                            datediff (
                                (
                                    select
                                        max(out_time)
                                    from
                                        tb_user_log
                                ),
                                min(in_time)
                            ) as first_log,
                            datediff (
                                (
                                    select
                                        max(out_time)
                                    from
                                        tb_user_log
                                ),
                                max(in_time)
                            ) as last_log
                        from
                            tb_user_log
                        group by
                            uid
                    ) as tb_log
            ) as tb_grade
        group by
            user_grade
    ) as tb_grade_num
order by
    ratio desc