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

# 1.按用户分组
# 2.计算每个用户最大进入日期与整个表最大进入日期之差，latest_time，
# 3.计算每个用户最小进入日期与整个表最大进入日期之差，last_time，
# 以上作为子表t1
# 4.last_time<=6 新晋用户标记2
# 4.latest_time<=6 忠实用户1
# 5.latest_time<=29 沉睡用户3
# 6.ELSE 流失用户4
# 7.以上作为子表t2
# 8.t1按用户等级分组，计算并输出
SELECT CASE u_grade
    WHEN 1 THEN '忠实用户'
    WHEN 2 THEN '新晋用户'
    WHEN 3 THEN '沉睡用户'
    ELSE '流失用户' END user_grade,
    ROUND(COUNT(uid)/(SELECT COUNT(DISTINCT uid) FROM tb_user_log),2) ratio
FROM 
    (SELECT uid,
        CASE WHEN last_time <= 6 THEN 2
            WHEN latest_time <= 6  THEN 1
            WHEN latest_time <= 29 THEN 3
        ELSE 4 END u_grade
    FROM 
        (SELECT uid,
        DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log),DATE(MAX(in_time))) latest_time,
        DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log),DATE(MIN(in_time))) last_time
        FROM tb_user_log
        GROUP BY uid
        ) t1
    ) t2
GROUP BY u_grade
ORDER BY ratio DESC