如何用 SQL 计算用户次日留存率？请说清口径与实现思路。

🎯 考察意图

留存是数据分析最高频指标之一。考察口径意识（按什么算「活跃」、分母是谁）和把业务定义落成可复现 SQL 的能力。

📐 回答框架

先问清/说清口径：活跃定义、新老用户、时区、是否去重。
明确分子分母：分母=D0 活跃（或新增）用户集合；分子=其中 D1 仍活跃。
给出实现路径：先取每日活跃用户表 → 自连接/日期偏移对齐 → 聚合。
提到常见变体：新增留存 vs 活跃留存、N 日留存、留存曲线。

💬 参考答案（口述版）

先定口径（口述时一定要先说，否则数字不可比）：
- 活跃：当日有启动 / 有关键事件（需与业务对齐）
- 对象：新增用户留存，还是全体活跃用户的次日回访
- 日期：自然日还是滚动 24h；时区用业务时区

经典「新增次日留存」：
1. 找出每个用户的首活日 first_date
2. 找出用户所有活跃日 active_date
3. 对首活日 = D 的用户，看是否存在 active_date = D+1
4. 留存率 = 有 D+1 活跃的用户数 / D 日新增用户数

实现上常用：每日活跃用户去重表 dau(user_id, dt)，再：

WITH first_day AS (
  SELECT user_id, MIN(dt) AS d0 FROM dau GROUP BY 1
)
SELECT
  f.d0,
  COUNT(DISTINCT f.user_id) AS new_users,
  COUNT(DISTINCT CASE WHEN d.dt = DATE_ADD(f.d0, 1) THEN f.user_id END) AS d1_retained,
  COUNT(DISTINCT CASE WHEN d.dt = DATE_ADD(f.d0, 1) THEN f.user_id END)
    * 1.0 / COUNT(DISTINCT f.user_id) AS d1_rate
FROM first_day f
LEFT JOIN dau d ON f.user_id = d.user_id
GROUP BY 1;

活跃留存则分母改为某日全体活跃用户，分子为次日仍活跃。

🔍 关键词

口径cohortDAU 去重DATE_ADD分子分母

💭 面试官可能的追问

追问 1：留存率突然下跌，你会怎么排查？

先核口径与数据质量（埋点、时区、去重）；再切片（渠道/版本/新老/机型）；对照发版与活动；看是否分母膨胀（拉新质量差）还是分子变少（产品体验/崩溃）。

追问 2：为什么有人用「队列留存」而不是「整体次日留存」？

队列（cohort）按获取日分组，能剥离「用户结构变化」对整体留存的干扰，更适合评估拉新质量与版本影响。

⚠️ 常见踩坑

分母用注册用户、分子用活跃用户，口径不一致
同一用户一天多次活跃未去重，导致虚高
跨时区把「次日」算错

🧠 记忆锚点

先对齐口径，再写 SQL；留存本质是两个用户集合的比值。