如何用SQL查询连续两个月使用产品的用户及月度留存统计

第一步：优化月度活跃用户CTE #

你的原CTE写法可以简化，避免不必要的数组嵌套和UNNEST操作，直接通过分组去重得到每月活跃用户列表，同时修正原代码中的语法错误（多余逗号）：

WITH monthly_active_users AS (
  -- 提取消息日期并按月、用户去重，得到每月至少登录一次的用户
  SELECT
    DATE_TRUNC(DATE(date), MONTH) AS month,  -- 将日期截断到当月第一天
    recipient AS user_id
  FROM messages
  GROUP BY month, user_id
  -- 过滤掉超过当前月份的数据
  HAVING month <= DATE_TRUNC(CURRENT_DATE(), MONTH)
)

如果你的SQL环境不支持DATE_TRUNC，可以用你原来的写法替换日期处理部分：

DATE(CONCAT(FORMAT_DATE("%Y-%m", DATE(date)), '-01')) AS month

第二步：补充留存用户统计逻辑 #

通过自连接将当前月的用户与下个月的用户关联，统计每个月的活跃用户数和留存到下月的用户数：

SELECT
  current.month,
  COUNT(DISTINCT current.user_id) AS monthly_active_count,  -- 当月活跃用户总数
  COUNT(DISTINCT next.user_id) AS retained_users_count      -- 当月用户中下月仍登录的人数
FROM monthly_active_users current
-- 左连接下月的活跃用户列表，匹配同一用户
LEFT JOIN monthly_active_users next
  ON current.user_id = next.user_id
  AND next.month = DATE_ADD(current.month, INTERVAL 1 MONTH)
GROUP BY current.month
ORDER BY current.month DESC;

完整代码整合 #

WITH monthly_active_users AS (
  SELECT
    DATE_TRUNC(DATE(date), MONTH) AS month,
    recipient AS user_id
  FROM messages
  GROUP BY month, user_id
  HAVING month <= DATE_TRUNC(CURRENT_DATE(), MONTH)
)
SELECT
  current.month,
  COUNT(DISTINCT current.user_id) AS monthly_active_count,
  COUNT(DISTINCT next.user_id) AS retained_users_count
FROM monthly_active_users current
LEFT JOIN monthly_active_users next
  ON current.user_id = next.user_id
  AND next.month = DATE_ADD(current.month, INTERVAL 1 MONTH)
GROUP BY current.month
ORDER BY current.month DESC;

关键说明 #

1. 自连接逻辑：current表代表当前月的活跃用户，next表代表下月的活跃用户，通过用户ID和月份差1个月的条件关联，找到在两个月都活跃的用户。
2. COUNT(DISTINCT)：确保每个用户只被统计一次，避免同一用户当月多次登录导致重复计数。
3. LEFT JOIN：保证即使某个月没有用户留存到下月，该月的数据仍会被输出（此时retained_users_count为0）。

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