如何计算Cohort用户订阅后6个月内的月度营收及相关指标？

需求说明 #

• 提取以【订阅日期（dateabo）+ pays + operateur + sspdt】为特征的用户群组（cohort）
• 每个cohort需计算：
  1. 订阅用户数量
  2. 该群组用户在订阅当月及之后5个月（共6个运营月）每个月产生的营收（revers）
• 新增index列，取值0-5，代表运营月与订阅月的月份差（index=0对应订阅当月，index=1对应订阅后第1个月，以此类推）

已知条件 #

• id为订阅用户ID，同时存在于表A和表B中
• revers是表B中DATE_REBILL日期下对应id产生的营收
• dateabo为订阅日期

原查询问题分析 #

原SQL仅能获取index=0的数据，原因是关联条件DATE_TRUNC('MONTH', TO_TIMESTAMP(r.DATE_REBILL)) = s.subscription_month仅限定营收月份等于订阅当月，无法覆盖订阅后后续5个月的营收数据。

修正后的SQL查询 #

SELECT
  s.subscription_month,
  s.pays,
  s.operateurname,
  s.sspdt,
  idx AS index,
  COUNT(DISTINCT s.id) AS subscriber_count,
  COALESCE(SUM(r.revers), 0) AS monthly_revenue,
  -- 避免除以0，营收为0时ARPU设为NULL
  CASE WHEN COALESCE(SUM(r.revers), 0) = 0 THEN NULL 
       ELSE COUNT(DISTINCT s.id) / COALESCE(SUM(r.revers), 0) 
  END AS arpu_local
FROM
  (
    SELECT
      DATE_TRUNC('MONTH', a.dateabo::DATE) AS subscription_month,
      o.pays,
      o.operateurname,
      a.sspdt,
      a.id
    FROM
      tableA AS a
    JOIN tableO AS o ON -- 原查询遗漏表o关联逻辑，请根据实际结构调整关联字段
      a.id = o.id
    WHERE
      o.pays = 'US'
      AND a.dateabo::DATE BETWEEN '2018-01-01' AND '2023-04-01'
    GROUP BY
      subscription_month, o.pays, o.operateurname, a.sspdt, a.id
  ) AS s
-- 生成0-5的月份差序列，覆盖6个运营月
CROSS JOIN generate_series(0, 5) AS idx
LEFT JOIN tableB AS r 
  ON s.id = r.id
  -- 匹配营收月份为订阅月份加上对应index的月数
  AND DATE_TRUNC('MONTH', TO_TIMESTAMP(r.DATE_REBILL)) = (s.subscription_month + (idx || ' months')::INTERVAL)
-- 过滤超出当前日期的无效月份
WHERE
  (s.subscription_month + (idx || ' months')::INTERVAL) <= DATE_TRUNC('MONTH', CURRENT_DATE)
GROUP BY
  s.subscription_month, s.pays, s.operateurname, s.sspdt, idx
ORDER BY
  s.subscription_month DESC, idx ASC;

关键修正点说明 #

1. 生成月份差序列：通过CROSS JOIN generate_series(0,5)为每个cohort生成0到5的索引值，确保每个群组都能覆盖6个运营月
2. 关联条件调整：将营收月份匹配逻辑改为动态计算订阅月加index月数，实现全周期营收关联
3. 数据完整性处理：用COALESCE处理无营收月份的NULL值，通过WHERE子句过滤超出当前日期的无效数据
4. 补充表关联：原查询遗漏表o的关联逻辑，已补充（请根据实际表结构调整关联字段）

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