月度留存率查询调整：修正留存率计算分母为上月总用户

原代码的留存率计算是返回用户数 / 当前月总用户数，要改成返回用户数 / 上月总用户数，需要调整统计逻辑，核心是获取每个月对应的上月总用户数，而非使用当月用户数作为分母。

修改后的完整代码 #

WITH monthly_user_counts AS (
  SELECT
    DATE_TRUNC(PARSE_DATE('%Y%m%d',event_date),MONTH) AS obs_date,
    user_pseudo_id
  FROM
    `rayn-deen-app.analytics_317927526.events_*`
),
-- 新增CTE：统计每个月的总用户数
monthly_totals AS (
  SELECT
    obs_date,
    COUNT(DISTINCT user_pseudo_id) AS monthly_total
  FROM
    monthly_user_counts
  GROUP BY
    obs_date
),
returning_users AS (
  SELECT
    curr.obs_date,
    COUNT(DISTINCT prev.user_pseudo_id) AS returning_user_count,
    -- 关联上月的总用户数
    prev_totals.monthly_total AS previous_month_total
  FROM
    monthly_user_counts curr
  LEFT JOIN
    monthly_user_counts prev
  ON
    curr.user_pseudo_id = prev.user_pseudo_id 
    AND prev.obs_date = DATE_SUB(curr.obs_date, INTERVAL 1 MONTH)
  -- 关联月度总用户统计，获取上月的总用户数
  LEFT JOIN
    monthly_totals prev_totals
  ON
    prev_totals.obs_date = DATE_SUB(curr.obs_date, INTERVAL 1 MONTH)
  GROUP BY
    curr.obs_date, prev_totals.monthly_total
),
inter AS (
  SELECT
    obs_date,
    returning_user_count,
    previous_month_total,
    -- 用返回用户数除以上月总用户数计算留存率
    round((returning_user_count * 100.0 / previous_month_total),1) AS monthly_retention
  FROM
    returning_users
  ORDER BY
    obs_date
)

SELECT * FROM inter;

关键改动说明 #

• 新增monthly_totals CTE：单独统计每个月的总用户数，后续用于获取上月的用户基数。
• 在returning_users中，通过LEFT JOIN关联monthly_totals，拿到当前月份对应的上月总用户数（previous_month_total）。
• 最终留存率计算时，将分母从原有的当月总用户数替换为previous_month_total。

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