用户留存率怎么算？从公式到SQL一篇讲清

SQL手册第 4 题约 8 分钟读完

本文目录

总结
先搞清楚：什么是留存率
数据准备
方法一：LEFT JOIN（最通用）
方法二：DATEDIFF + 条件聚合（更优雅）
实战经验：我踩过的坑
面试加分：留存的业务理解

总结

方法	优点	缺点	适用场景
------	------	------	----------
多次LEFT JOIN	直观易懂	留存周期多时SQL冗长	面试快速作答
DATEDIFF + 条件聚合	一次JOIN搞定	理解成本稍高	实际工作推荐

留存率这道题，SQL写法只是基础，能聊到时区处理、性能优化、业务理解，才能拿到高分。

明天 05 聊累计求和与滑动窗口，这是窗口函数的高阶用法，在广告ROI分析和财务报表里用得非常多。

你们公司的留存口径是怎么定义的？有没有遇到过坑？评论区交流～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

先把这段讲顺；面试官追问时，再展开下面的原理和细节。

详细讲解

留存率是互联网公司最核心的指标之一，也是SQL面试中业务属性最强的一道题。光会写SQL不够，你还得理解"留存"这个业务概念本身。今天我从业务定义讲起，一步步写到SQL。

先搞清楚：什么是留存率

留存率 = 某天新增的用户中，在之后第N天仍然活跃的比例。

举个例子：1月1日新增了1000个用户，其中有350个用户在1月2日还来了，那次日留存率 = 350 / 1000 = 35%。

常见的留存口径：

次日留存（Day 1）： 注册后第2天还来的比例

3日留存（Day 3）： 注册后第4天还来的比例（注意是第4天，不是前3天内）

7日留存（Day 7）： 注册后第8天

30日留存（Day 30）： 注册后第31天

注意： N日留存看的是"第N+1天那一天"的活跃情况，不是"前N天的累计"。这个口径很多人搞混，面试的时候说清楚是加分项。

数据准备

假设有两张表：

-- 用户注册表
user_register(user_id, register_date)
 
-- 用户活跃日志（已去重到天级别）
user_active(user_id, active_date)

方法一：LEFT JOIN（最通用）

思路：把注册用户和第N天的活跃数据做LEFT JOIN，能JOIN上的就是留存用户。

计算某一天的次日留存率：

SELECT
  r.register_date,
  COUNT(DISTINCT r.user_id) AS new_users,
  COUNT(DISTINCT a.user_id) AS retained_users,
  ROUND(
    COUNT(DISTINCT a.user_id) * 100.0 / COUNT(DISTINCT r.user_id),
    2
  ) AS day1_retention_rate
FROM user_register r
LEFT JOIN user_active a
  ON r.user_id = a.user_id
  AND a.active_date = DATE_ADD(r.register_date, 1)  -- 第2天
WHERE r.register_date = '2025-01-01'
GROUP BY r.register_date

同时算次日、3日、7日留存：

SELECT
  r.register_date,
  COUNT(DISTINCT r.user_id) AS new_users,
  COUNT(DISTINCT d1.user_id) AS day1_retained,
  COUNT(DISTINCT d3.user_id) AS day3_retained,
  COUNT(DISTINCT d7.user_id) AS day7_retained,
  ROUND(COUNT(DISTINCT d1.user_id) * 100.0 / COUNT(DISTINCT r.user_id), 2) AS day1_rate,
  ROUND(COUNT(DISTINCT d3.user_id) * 100.0 / COUNT(DISTINCT r.user_id), 2) AS day3_rate,
  ROUND(COUNT(DISTINCT d7.user_id) * 100.0 / COUNT(DISTINCT r.user_id), 2) AS day7_rate
FROM user_register r
LEFT JOIN user_active d1
  ON r.user_id = d1.user_id AND d1.active_date = DATE_ADD(r.register_date, 1)
LEFT JOIN user_active d3
  ON r.user_id = d3.user_id AND d3.active_date = DATE_ADD(r.register_date, 3)
LEFT JOIN user_active d7
  ON r.user_id = d7.user_id AND d7.active_date = DATE_ADD(r.register_date, 7)
WHERE r.register_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY r.register_date
ORDER BY r.register_date

这种方法直观，面试写出来完全够用。但缺点是每多算一个留存周期就多一次JOIN，留存周期多了SQL会很长。

方法二：DATEDIFF + 条件聚合（更优雅）

思路：先把注册表和活跃表做一次JOIN，算出日期差，然后用条件聚合一次性算出所有留存。

SELECT
  r.register_date,
  COUNT(DISTINCT r.user_id) AS new_users,
  COUNT(DISTINCT CASE WHEN DATEDIFF(a.active_date, r.register_date) = 1
    THEN a.user_id END) AS day1_retained,
  COUNT(DISTINCT CASE WHEN DATEDIFF(a.active_date, r.register_date) = 3
    THEN a.user_id END) AS day3_retained,
  COUNT(DISTINCT CASE WHEN DATEDIFF(a.active_date, r.register_date) = 7
    THEN a.user_id END) AS day7_retained,
  COUNT(DISTINCT CASE WHEN DATEDIFF(a.active_date, r.register_date) = 30
    THEN a.user_id END) AS day30_retained
FROM user_register r
LEFT JOIN user_active a
  ON r.user_id = a.user_id
  AND a.active_date BETWEEN r.register_date AND DATE_ADD(r.register_date, 30)
WHERE r.register_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY r.register_date
ORDER BY r.register_date

只做了一次JOIN，用CASE WHEN区分不同的留存天数。新增留存周期只需要加一行CASE WHEN，不用加JOIN。

关键优化点： JOIN条件里加了 a.active_date BETWEEN ... AND DATE_ADD(r.register_date, 30) 来限制JOIN的范围，避免全表关联。没有这个条件的话，大数据量下JOIN会爆炸。

实战经验：我踩过的坑

我在做出海短剧App留存分析的时候，遇到过几个典型问题：

坑1：注册和活跃的时区不一致

我们的用户遍布美国、东南亚、中东，注册时间是UTC，但运营要看的是"用户当地时间"的留存。如果直接用UTC算，美国用户在当地时间1月1号晚上注册的，UTC已经是1月2号了，这样次日留存会虚高。

解法：在计算之前先统一转成用户所在时区的日期。

-- 根据用户所在地区转换时区
DATE(CONVERT_TZ(register_time, 'UTC', user_timezone)) AS register_date

坑2：新用户当天也算活跃

有些公司的活跃日志里，注册当天的登录也会被记录。如果不注意，DATEDIFF=0 的数据会干扰计算。要么在注册表里排除当天活跃，要么在计算时明确只看DATEDIFF >= 1的数据。

坑3：大表JOIN性能问题

用户活跃表通常是亿级别的，直接和注册表全量JOIN非常慢。实际工作中我们会先把活跃表按日期分区过滤，只取需要的日期范围：

-- 比如算1月份新用户的7日留存，活跃表只需要取1月1日到2月7日的数据
AND a.dt BETWEEN '2025-01-01' AND '2025-02-07'

分区裁剪能减少90%以上的数据扫描量。这个优化意识面试官很看重。

面试加分：留存的业务理解

面试官可能会追问业务相关的问题，比如：

Q：留存率多少算好？

不同行业差异很大。社交App的次日留存能到40%-50%就不错了，工具类App可能只有20%。短剧类产品的次日留存一般在15%-25%之间，因为用户看完一部就走了，拉回来主要靠推送新剧。

Q：留存低了怎么分析？

按维度拆解：分渠道（是某个买量渠道质量差）、分地区（是某个市场水土不服）、分版本（是某次更新导致体验变差）。这个分析思路比SQL本身更重要。

Q：留存和DAU什么关系？

DAU = 历史每天新增用户 × 对应的留存率，累加起来。留存率决定了你花钱买来的用户能留下多少，直接影响LTV和ROI。

刷题进度保存在当前浏览器