修改SQL查询：统计特定周订阅用户后续6周的Churn与Retention

原查询仅支持单静态周的流失统计，要实现按订阅起始周分组并追踪后续6周的留存，需做以下核心调整：

• 按订阅起始周分组，统计每组的新增订阅用户
• 为每个起始周生成后续6个观测周，逐一判断用户是否在该周留存
• 聚合计算各周的留存/流失数量及比例

修改后的SQL代码 #

WITH user_subscription_weeks AS (
  -- 提取每个用户的订阅起始周、订阅时间范围
  SELECT
    DISTINCT user_pseudo_id,
    DATE_TRUNC('week', subscription_start) AS signup_week, -- 按周聚合订阅起始时间（默认周一为周起始）
    subscription_start,
    COALESCE(subscription_end, CURRENT_DATE()) AS subscription_end -- 未结束的订阅视为持续到当前日期
  FROM datatable
),

-- 生成后续6个观测周的偏移量（1=第1周后，…6=第6周后）
week_offsets AS (
  SELECT offset 
  FROM UNNEST(GENERATE_ARRAY(1, 6)) AS offset
),

-- 关联用户与观测周，标记留存状态
user_retention AS (
  SELECT
    usw.signup_week,
    wo.offset AS week_after_signup,
    usw.user_pseudo_id,
    -- 判断用户在观测周是否留存：订阅周期覆盖该周结束日期
    CASE
      WHEN usw.subscription_end > DATE_ADD(usw.signup_week, INTERVAL wo.offset WEEK) THEN 1
      ELSE 0
    END AS is_retained
  FROM user_subscription_weeks usw
  CROSS JOIN week_offsets wo
),

-- 统计各起始周的新增用户总数
signup_counts AS (
  SELECT
    signup_week,
    COUNT(DISTINCT user_pseudo_id) AS total_signups
  FROM user_subscription_weeks
  GROUP BY signup_week
)

-- 最终输出各起始周的6周留存/流失数据
SELECT
  sc.signup_week,
  sc.total_signups,
  ur.week_after_signup,
  SUM(ur.is_retained) AS retained_users,
  sc.total_signups - SUM(ur.is_retained) AS churned_users,
  ROUND(SUM(ur.is_retained) / sc.total_signups * 100, 2) AS retention_rate
FROM signup_counts sc
JOIN user_retention ur ON sc.signup_week = ur.signup_week
GROUP BY sc.signup_week, sc.total_signups, ur.week_after_signup
ORDER BY sc.signup_week, ur.week_after_signup;

关键逻辑说明 #

1. 订阅起始周分组：通过DATE_TRUNC('week', subscription_start)将用户按订阅所在周归类，确保同一周期的用户被统一统计。若需调整周起始日（如改为周日），可在函数中指定参数（例如BigQuery：DATE_TRUNC('week', subscription_start, 'SUNDAY')）。
2. 未结束订阅处理：用COALESCE(subscription_end, CURRENT_DATE())将未取消的订阅视为持续到当前日期，避免后续周的留存判断遗漏活跃用户。
3. 观测周生成：通过GENERATE_ARRAY快速生成后续6周的偏移量，若使用不支持该函数的SQL方言（如MySQL），可替换为手动生成：

   SELECT 1 AS offset UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
   

4. 留存判断：对比用户订阅结束时间与观测周结束日期，若订阅仍在有效期内则标记为留存。

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