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

SELECT 
    user_grade, 
    ROUND(COUNT(uid) / MAX(user_cnt), 2) as ratio
FROM 
(
    SELECT 
        uid, 
        user_cnt,
        CASE
            WHEN last_dt_diff >= 30 THEN "流失用户"
            WHEN last_dt_diff >= 7 THEN "沉睡用户"
            WHEN first_dt_diff < 7 THEN "新晋用户"
            ELSE "忠实用户"
        END as user_grade    # 每个用户的活跃等级
    FROM 
    (
        SELECT 
            uid, 
            user_cnt,
            TIMESTAMPDIFF(DAY,first_dt,cur_dt) as first_dt_diff,    # 最早活跃距今天数
            TIMESTAMPDIFF(DAY,last_dt,cur_dt) as last_dt_diff       # 最晚（最近）活跃距今天数
        FROM 
        (
            SELECT 
                uid, 
                MIN(DATE(in_time)) as first_dt, # 统计最早活跃
                MAX(DATE(out_time)) as last_dt  # 统计最晚活跃
            FROM tb_user_log
            GROUP BY uid
        ) as t_uid_first_last  
        LEFT JOIN 
        (
            SELECT 
                MAX(DATE(out_time)) as cur_dt,   # 获取当前日期
                COUNT(DISTINCT uid) as user_cnt  # 统计总用户数
            FROM tb_user_log
        ) as t_overall_info 
        ON 1        # on 1 是将右表的结果添加到左表的每一行上面
    ) as t_user_info
) as t_user_grade
GROUP BY user_grade
ORDER BY ratio DESC;