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

WITH 
    -- 合并活跃日期计算和边界计算
    user_activity_data AS (
        SELECT 
            uid,
            DATE(in_time) AS active_date
        FROM tb_user_log
        UNION
        SELECT 
            uid,
            DATE(out_time) AS active_date
        FROM tb_user_log
    ),
    
    -- 计算日期边界和用户活跃情况
    user_stats AS (
        SELECT 
            uid,
            MIN(active_date) AS first_active_date,
            MAX(active_date) AS last_active_date,
            (SELECT MAX(active_date) FROM user_activity_data) AS today,
            (SELECT DATE_SUB(MAX(active_date), INTERVAL 6 DAY) FROM user_activity_data) AS seven_days_ago,
            (SELECT DATE_SUB(MAX(active_date), INTERVAL 29 DAY) FROM user_activity_data) AS thirty_days_ago
        FROM user_activity_data
        GROUP BY uid
    ),
    
    -- 直接分类用户
    user_classification AS (
        SELECT
            uid,
            CASE
                WHEN first_active_date >= seven_days_ago THEN '新晋用户'
                WHEN last_active_date >= seven_days_ago THEN '忠实用户'
                WHEN last_active_date >= thirty_days_ago THEN '沉睡用户'
                ELSE '流失用户'
            END AS user_grade
        FROM user_stats
    )

-- 计算各类用户占比
SELECT
    user_grade,
    ROUND(COUNT(*) / (SELECT COUNT(*) FROM user_classification), 2) AS ratio
FROM user_classification
GROUP BY user_grade
ORDER BY ratio DESC, user_grade;