题解 | 统计活跃间隔对用户分级结果 简单易懂！

# 新晋用户：从当天开始近7天新增用户 DATEDIFF('当天', '用户最早登录时间') <= 6
# 忠实用户：从当天开始近7天活跃用户但非新晋用户 直接ELSE就完事儿了
# 沉睡用户：从当天开始7天到30天内活跃过的用户 DATEDIFF('当天', '用户最晚登出时间') BETWEEN 7 AND 29
# 流失用户：从当天开始30天之前活跃过的用户 DATEDIFF('当天', '用户最晚登出时间') > 29

SELECT
    user_grade,
    ROUND(COUNT(user_grade) / (SELECT COUNT(DISTINCT uid) FROM tb_user_log), 2) AS ratio # 计算ratio
FROM(
SELECT # 第二步 使用 CASE WHEN 进行用户分类
    uid,
CASE
    WHEN DATEDIFF((SELECT MAX(DATE(out_time)) FROM tb_user_log), reg_dt) <= 6 THEN '新晋用户'
    WHEN DATEDIFF((SELECT MAX(DATE(out_time)) FROM tb_user_log), recent_dt) BETWEEN 7 AND 29 THEN '沉睡用户'
    WHEN DATEDIFF((SELECT MAX(DATE(out_time)) FROM tb_user_log), recent_dt) > 29 THEN '流失用户'
    ELSE '忠实用户'
END AS user_grade
FROM(
    SELECT # 第一步先创建最早登录，最晚登出时间表
        uid,
        MIN(DATE(in_time)) AS reg_dt, # 用户最早登录时间
        MAX(DATE(out_time)) AS recent_dt # 用户最晚登出时间
    FROM tb_user_log
    GROUP BY uid 
) AS tb1
) AS tb2
GROUP BY user_grade
ORDER BY ratio DESC