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

WITH
    hebing AS ( --第一步是进行合并数据，有的用户有可能是今天进第二天出，所以最好将代码进行一个合并，然后去重，保留唯一数据
        SELECT DISTINCT
            uid,
            DATE_FORMAT(in_time, '%Y-%m-%d') AS log_date
        FROM
            tb_user_log
        WHERE
            in_time IS NOT NULL
        UNION
        SELECT DISTINCT
            uid,
            DATE_FORMAT(out_time, '%Y-%m-%d') AS log_date
        FROM
            tb_user_log
        WHERE
            out_time IS NOT NULL
    ),--这里是求出最大天数，并且用窗口函数创建一列放最大的日期
    zuida_date AS (
        SELECT
            uid,
            log_date,
            MAX(log_date) OVER () AS max_date
        FROM
            hebing
    ),-这里邱最大日期和活跃日期的一个差值
    R_date_cha AS (
        SELECT
            *,
            DATEDIFF(max_date, log_date) AS date_diff
        FROM
            zuida_date
    ),这里采用逻辑去判断这条记录是近期天的还是 近7-30天的还是近30天前的
    user_active_counts AS (
        SELECT
            uid,
            SUM(
                CASE
                    WHEN date_diff < 7 THEN 1
                    ELSE 0
                END
            ) AS active_7d,
            SUM(
                CASE
                    WHEN date_diff >= 7
                    AND date_diff < 30 THEN 1
                    ELSE 0
                END
            ) AS active_7_30d,
            SUM(
                CASE
                    WHEN date_diff >= 30 THEN 1
                    ELSE 0
                END
            ) AS active_30d_plus
        FROM
            R_date_cha
        GROUP BY
            uid
    ),
    user_layer AS (
        SELECT
            uid,
            CASE
                WHEN active_7d > 0
                AND (
                    active_7_30d > 0
                    OR active_30d_plus > 0
                ) THEN '忠实用户'
                WHEN active_7d > 0
                AND active_7_30d = 0
                AND active_30d_plus = 0 THEN '新晋用户'
                WHEN active_7d = 0
                AND active_7_30d > 0
                AND active_30d_plus = 0
                 THEN '沉睡用户'
                WHEN active_7d = 0
                AND active_7_30d = 0
                AND active_30d_plus > 0 THEN '流失用户'
                ELSE '其他'
            END AS user_grade
        FROM
            user_active_counts
    ),
    summary AS (
        SELECT
            user_grade,
            COUNT(*) AS user_count
        FROM
            user_layer
        GROUP BY
            user_grade
    )
    # ,
    # total AS (
    #     SELECT
    #         SUM(user_count) AS total_users
    #     FROM
    #         summary
    # )
SELECT
    s.user_grade,
    ROUND(s.user_count /(SELECT SUM(user_count) FROM summary) , 2) AS ratio
FROM
    summary s
    # JOIN total t ON 1 = 1
ORDER BY ratio DESC;