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

SELECT user_grade, ROUND(COUNT(uid) / MAX(user_count), 2)  ratio
FROM (
    SELECT uid, user_count,
        (CASE 
            WHEN leave_day >= 30 THEN '流失用户'
            WHEN leave_day >= 7 THEN '沉睡用户'
            WHEN come_day < 7 THEN '新晋用户'
            ELSE '忠实用户'
        END) AS user_grade
    FROM (
        SELECT uid, user_count,
            TIMESTAMPDIFF(day, max_out, td)  leave_day,
            TIMESTAMPDIFF(day, min_in, td)  come_day
        FROM (
            SELECT uid,
                MAX(DATE(out_time))  max_out,
                MIN(DATE(in_time))  min_in
            FROM tb_user_log
            GROUP BY uid
        ) AS in_out
        LEFT JOIN (
            SELECT MAX(DATE(out_time))  td,
                COUNT(DISTINCT uid)  user_count
            FROM tb_user_log
        ) AS td_count
        ON 1=1
    )  user_info
)  grade
GROUP BY user_grade
ORDER BY ratio DESC;

活跃间隔=当前时间-最新的登出时间；

新晋用户（近七日新增）=当前时间-第一次登入时间<7