核心要点

  • 留存定义：以「首次活跃日」为基准 t0，第 N 日仍活跃的用户数 / t0 当日活跃用户数

  • 先用 MIN(活跃日期) GROUP BY 用户求出每个用户的首日，作为留存的对齐基准

  • 自连接或 EXISTS 判断该用户在 t0 + N 天是否还有活跃记录，用 DATEDIFF/日期差匹配

  • 易错点：留存分母是首日活跃用户而非全部用户；次日留存的 N=1，7 日留存通常指 N=6 或 N=7，需对齐口径

标准回答

留存率必须以用户的首次活跃日为基准对齐。先按用户聚合求出 first_day，再把活跃明细表自连接回来，用日期差判断该用户在首日之后第 N 天是否还有活跃记录。分子是「首日活跃且第 N 日回访」的去重用户数，分母是「首日活跃」的去重用户数。次日留存对应 datediff=1，7 日留存按业务口径取 datediff=6 或 7，面试时要主动说明口径。

sql
-- 按首日分组，计算次日(D1)和 7 日(D7)留存率
WITH first_active AS (        -- 每个用户的首次活跃日
  SELECT user_id, MIN(active_date) AS first_day
  FROM user_activity
  GROUP BY user_id
)
SELECT
  f.first_day,
  COUNT(DISTINCT f.user_id) AS new_users,   -- 首日活跃用户(分母)
  -- 次日留存：存在 first_day + 1 天的活跃记录
  COUNT(DISTINCT CASE
    WHEN DATEDIFF(a.active_date, f.first_day) = 1 THEN f.user_id END
  ) * 1.0 / COUNT(DISTINCT f.user_id) AS d1_retention,
  -- 7 日留存：存在 first_day + 7 天的活跃记录
  COUNT(DISTINCT CASE
    WHEN DATEDIFF(a.active_date, f.first_day) = 7 THEN f.user_id END
  ) * 1.0 / COUNT(DISTINCT f.user_id) AS d7_retention
FROM first_active f
LEFT JOIN user_activity a       -- 关回明细看后续是否回访
  ON f.user_id = a.user_id
GROUP BY f.first_day
ORDER BY f.first_day;

常见误区

⚠️ 常见踩坑

分母用「全部活跃用户数」而非「首日新增用户数」会算错；用 COUNT 而非 COUNT(DISTINCT) 会因单日多次活跃被重复计数；以及 D1 写成普通 JOIN 会漏掉未回访用户导致留存虚高，应用 LEFT JOIN + 条件聚合。

追问

追问 1：如果数据量很大，自连接很慢，有什么优化思路？

可改用 EXISTS 半连接避免行放大：对每个首日用户判断 EXISTS(SELECT 1 FROM activity WHERE user_id=f.user_id AND active_date=DATE_ADD(first_day, INTERVAL 1 DAY)) 即可，命中即停、不展开。也可预先按用户+日期建活跃位图（bitmap）或对 user_id、active_date 建联合索引；离线场景常用每日全量活跃表打标后按 cohort 汇总，避免明细自连接。

追问 2：「7 日留存率」和「7 日内留存率」有什么区别？

「第 7 日留存」是精确第 7 天（datediff=7）当天活跃；「7 日内留存」(unbounded/滚动) 通常指首日之后 7 天内任意一天回访过即算留存，条件改为 DATEDIFF BETWEEN 1 AND 7。两者口径差异很大，面试和实际取数前必须先与业务确认定义，否则结论会偏差明显。

延伸学习

与本题相关的知识库文章、术语、工具与行业资讯。

📖 术语表

🎯 相关面试题