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

-- 步骤 1：查找每个用户的最早日志记录
WITH tmp_fst AS (
    SELECT
        uid,
        MIN(in_time) AS in_time
    FROM
        tb_user_log
    GROUP BY
        uid
),

-- 步骤 2：查找具有多个日志记录的用户的最新日志记录
tmp_non_lst AS (
    SELECT
        uid,
        MAX(in_time) AS in_time
    FROM
        tb_user_log
    GROUP BY
        uid
    HAVING
        MAX(in_time) <> MIN(in_time)
),

-- 步骤 3：根据日期差异计算用户等级
result_tmp AS (
    SELECT
        CASE
            -- "流失用户"：最后一次日志记录至少在 29 天前的用户
            WHEN DATEDIFF(c.today, COALESCE(b.in_time, a.in_time)) >= 29 THEN "流失用户"
            -- "沉睡用户"：最后一次日志记录至少在 6 天前但不超过 29 天的用户
            WHEN DATEDIFF(c.today, COALESCE(b.in_time, a.in_time)) >= 6 THEN "沉睡用户"
            -- "新晋用户"：首次日志记录在过去 6 天内的用户
            WHEN DATEDIFF(c.today, a.in_time) <= 6 THEN "新晋用户"
            -- "忠实用户"：最后一次日志记录在过去 6 天内且至少有一条之前的记录的用户
            WHEN DATEDIFF(c.today, b.in_time) <= 6 AND b.in_time IS NOT NULL THEN "忠实用户"
        END AS user_grade
    FROM
        tmp_fst a
    LEFT JOIN
        tmp_non_lst b ON a.uid = b.uid
    JOIN
        (SELECT MAX(in_time) AS today FROM tb_user_log) c ON 1 = 1
)

-- 步骤 4：计算每个用户等级的比例
SELECT
    t1.user_grade,
    t1.ratio
FROM
    (
    SELECT
        t.user_grade,
        ROUND(COUNT(t.user_grade) OVER (PARTITION BY t.user_grade ORDER BY t.user_grade) / COUNT(t.user_grade) OVER (), 2) AS ratio
    FROM
        result_tmp t
    ) t1
GROUP BY
    t1.user_grade,
    t1.ratio;