如何编写Snowflake SnowSQL滚动月度用户复活统计SQL查询

实现思路 #

• 先聚合得到每个用户的所有活跃月份，过滤无效的非活跃记录，减少数据计算量
• 对每个用户的活跃月份按时间排序，用窗口函数计算相邻两次活跃的月份间隔
• 当相邻两次活跃间隔≥2个月时，后一次活跃的月份即为该用户的复活月（符合间隔至少1个完整自然月无活跃、历史有活跃、当月有活跃的判定规则）
• 最后按月份聚合，统计每个月的复活用户数，无复活的月份自动补0

完整SnowSQL代码 #

WITH 
-- 步骤1：获取所有需要统计的候选月份（所有存在活跃记录的自然月）
all_calendar_months AS (
    SELECT DISTINCT DATE_TRUNC('MONTH', dt) AS stat_month
    FROM F_ACTIVITY
    WHERE is_active = 1
),
-- 步骤2：聚合得到每个用户的所有活跃月份（去重，每个用户每个活跃月仅存1条）
user_active_months AS (
    SELECT 
        user_id,
        DATE_TRUNC('MONTH', dt) AS active_month
    FROM F_ACTIVITY
    WHERE is_active = 1
    GROUP BY user_id, DATE_TRUNC('MONTH', dt)
),
-- 步骤3：标记每个用户的复活月份
user_reactivations AS (
    SELECT 
        user_id,
        active_month AS reactivation_month
    FROM (
        SELECT 
            user_id,
            active_month,
            -- 取该用户上一次的活跃月份
            LAG(active_month, 1) OVER (PARTITION BY user_id ORDER BY active_month) AS last_active_month
        FROM user_active_months
    ) t
    -- 满足间隔≥2个月，即中间至少隔了1个完整自然月无活跃
    WHERE DATEDIFF('MONTH', last_active_month, active_month) >= 2
)
-- 步骤4：按月份聚合得到最终统计结果
SELECT 
    TO_CHAR(acm.stat_month, 'YYYY-MM') AS month,
    COUNT(DISTINCT ur.user_id) AS reactivations_this_month
FROM all_calendar_months acm
LEFT JOIN user_reactivations ur 
    ON acm.stat_month = ur.reactivation_month
GROUP BY acm.stat_month
ORDER BY acm.stat_month;

逻辑验证 #

以你给出的2021年3月复活判定规则为例：

• 用户2021年1月及之前有活跃 → 存在last_active_month≤2021-01
• 2021年2月无活跃 → last_active_month与2021-03的间隔≥2个月
• 2021年3月有活跃 → active_month为2021-03
  完全匹配复活判定条件，统计结果准确。

内容的提问来源于stack exchange，提问作者MAL0UDA