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

WITH min_day AS (
    SELECT uid, DATE(MIN(in_time)) AS mday
    FROM tb_user_log
    GROUP BY uid
)

SELECT user_grade,
    ROUND(COUNT(DISTINCT uid) / (SELECT COUNT(DISTINCT uid) FROM tb_user_log WHERE in_time IS NOT NULL), 2) AS radio
FROM(
    -- 筛选忠实用户
    SELECT t_u_l.uid AS uid, '忠实用户' AS user_grade
    FROM tb_user_log t_u_l 
        JOIN min_day m_d
        USING(uid)
    WHERE DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log), DATE(in_time)) <= 6
        AND DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log), mday) > 6
    UNION ALL
    -- 筛选新晋用户
    SELECT t_u_l.uid AS uid, '新晋用户' AS user_grade
    FROM tb_user_log t_u_l
        JOIN min_day m_d
        USING(uid)
    WHERE DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log), mday) <= 6
    UNION ALL
    -- 筛选沉睡用户
    SELECT t_u_l.uid AS uid, '沉睡用户' AS user_grade
    FROM tb_user_log t_u_l
        JOIN min_day m_d
        USING(uid)
    WHERE t_u_l.uid NOT IN (
        SELECT uid
        FROM tb_user_log
        WHERE DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log), DATE(in_time)) <= 6
    ) AND DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log), mday) <= 29
    UNION ALL
    -- 筛选流失用户
    SELECT t_u_l.uid AS uid, '流失用户' AS user_grade
    FROM tb_user_log t_u_l
        JOIN min_day m_d
        USING(uid)
    WHERE t_u_l.uid NOT IN (
        SELECT uid
        FROM tb_user_log
        WHERE DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log), DATE(in_time)) <= 29
    ) AND DATEDIFF((SELECT DATE(MAX(in_time)) FROM tb_user_log), mday) > 29
)grade_t
GROUP BY user_grade
ORDER BY radio DESC