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

WITH t1 AS (
    SELECT uid,
           in_time
    FROM tb_user_log
    UNION ALL
    SELECT uid,
           out_time AS in_time
    FROM tb_user_log
),
t2 AS (
    SELECT uid,
           in_time,
           RANK() over (PARTITION BY uid ORDER BY uid ASC, in_time ASC ) AS num_1
    FROM t1
),
t3 AS (
    # 获取最大时间
    SELECT
           max(DATE(in_time)) AS max_date
    FROM t2
),
t4 AS (
    # 获取时间节点，7d，30d前
    SELECT max_date,
           DATE_ADD(max_date, INTERVAL -6 DAY ) AS last_7d,
           DATE_ADD(max_date, INTERVAL -29 DAY ) AS last_30d
    FROM t3
),
t5 AS (
    SELECT *
    FROM t2
    JOIN t4
),
t6 AS (
    # 近七日新晋用户情况
    SELECT uid
    FROM t5
    WHERE num_1 = 1 AND DATE (in_time) BETWEEN last_7d AND max_date
    GROUP BY uid
),
t7 AS (
    # 近7天活跃过且非新晋用户
    SELECT uid
    FROM t5
    WHERE DATE (in_time) BETWEEN last_7d AND max_date
    AND uid not in (SELECT uid FROM t6)
    GROUP BY uid
),
t8 AS (
    # 30d内活跃的用户
    SELECT uid
    FROM t5
    WHERE DATE(in_time) BETWEEN last_30d AND max_date
    GROUP BY uid
),
t9 AS (
    # 30d前活跃的用户
    SELECT uid
    FROM t5
    WHERE DATE(in_time) < last_30d
),
t10 AS (
    # 流失用户
    SELECT uid
    FROM t9
    WHERE uid NOT IN (SELECT uid FROM t8)
    GROUP BY uid
),
t11 AS (
    # 7d内活跃的用户
    SELECT uid
    FROM t5
    WHERE DATE(in_time) BETWEEN last_7d AND max_date
    GROUP BY uid
),
t12 AS (
    # 7d前活跃的用户
    SELECT uid
    FROM t5
    WHERE DATE(in_time) < last_7d
),
t13 AS (
    # 流失用户
    SELECT uid
    FROM t12
    WHERE uid NOT IN (SELECT uid FROM t11) AND uid NOT IN (SELECT uid FROM t10)
    GROUP BY uid
),
t14 AS (
    SELECT '忠实用户'     AS user_grade,
           count(uid) AS user_num
    FROM t7
    UNION ALL
    SELECT '新晋用户'     AS user_grade,
           count(uid) AS user_num
    FROM t6
    UNION ALL
    SELECT '沉睡用户'     AS user_grade,
           count(uid) AS user_num
    FROM t10
    UNION ALL
    SELECT '流失用户'     AS user_grade,
           count(uid) AS user_num
    FROM t13
),
t15 AS (
    SELECT SUM(user_num) AS sum_num
    FROM t14
),
t16 AS (
    SELECT t14.user_grade,
           ROUND(t14.user_num / t15.sum_num,2) AS ratio
           FROM t14
    JOIN t15
)
SELECT * FROM t16;

# 这道题的关键在于如何基于规则进行构建，而且关键时间节点很重要，可以提前计算！