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

SELECT
    CASE
        WHEN DATEDIFF(today,register_day) <= 6 THEN '新晋用户'
        WHEN DATEDIFF(today,last_active_day) <= 6 THEN '忠实用户'
        WHEN DATEDIFF(today,last_active_day) <= 29 THEN '沉睡用户'
        ELSE '流失用户'
    END user_grade,
    ROUND(COUNT(*) / SUM(COUNT(*)) OVER(),2) ratio
FROM(
    SELECT
        DISTINCT uid,
        MAX(out_time) OVER() today,
        MAX(out_time) OVER(PARTITION BY uid) last_active_day,
        MIN(in_time) OVER(PARTITION BY uid) register_day
    FROM
        tb_user_log ul
)t
GROUP BY
    user_grade
ORDER BY
    ratio DESC, user_grade