全球#SQL留存#求职准备#面试与材料

SQL 留存分析面试：分群、日期窗口与分母怎样算清楚

用可手算用户事件与SQLite查询练习第七日留存，说明注册分群、去重、行为、时区与成熟分母，加入滚动窗口和加权反例帮助解释SQL结果。

更新于 2026 年 10 月·约 8 分钟阅读·2,224 字

正在准备SQL留存，先补哪一步？

如果 SQL 能写出结果，但面试时解释不清口径，加微信说一下目标数据岗和最常出错的题型，可以先把专项训练范围定下来。

文 / STEM Career 编辑部

留存分析的 SQL 题，看起来经常只有几行查询，真正容易出错的却是分群、日期和分母。同一组事件，按日历日、滚动时间或不同活跃定义计算，结果可能不同。

本文用一组可以手算的原创数据，练习注册后第七个日历日的目标行为留存，并给出 SQLite 查询。先讲清口径，再写代码，面试时才能解释结果为什么成立。

先固定本题定义

用户按注册日期分群。目标行为是 action；第七日指注册日期加七个日历日，不包含注册当天；只有第七日已完整结束的用户进入分母。所有时间已按题设统一为 UTC，观测截止为 2026 年 10 月 10 日，不包含这一天。

因此，10 月 2 日注册的用户，第七日是 10 月 9 日，可以观察；10 月 3 日注册的用户，第七日是 10 月 10 日，尚未完整进入本次窗口，应排除。

这是一种教学定义，不是唯一留存标准。真实题目应先澄清目标行为、注册还是首次活跃分群、时间区间和成熟窗口。

原创用户与事件数据

用户	注册日	目标行为日期
A	10 月 1 日	10 月 8 日两次
B	10 月 1 日	10 月 7 日
C	10 月 2 日	10 月 9 日一次
D	10 月 2 日	无
E	10 月 3 日	本次观测未成熟

A 的两次事件只算一个留存用户；B 在第六日发生行为，不算本题第七日；C 符合；D 不符合；E 排除出分母。

手算结果应为 10 月 1 日组两人中一人，第七日留存 0.5；10 月 2 日组同样为 0.5。没有成熟窗口的分群不显示为零留存。

先建一张用户粒度的结果表

最重要的中间结果是每位成熟用户一行，标记是否在目标日发生行为。不要直接连接事件后用 COUNT(*) 计算人数，否则 A 的重复行为会扩大记录数。

没有行为的用户仍应保留，因此不能用只保留匹配事件的连接方式。先固定分母人群，再计算标记，查询会更容易解释与复核。

SQLite 日期函数文档说明了 date 等函数的用法。下方示例使用其方言；换到 PostgreSQL 或其他数据库时，需要按对应日期函数调整实现。

可运行的教学查询

WITH users(user_id, signup_day) AS (
  VALUES ('A','2026-10-01'),('B','2026-10-01'),
         ('C','2026-10-02'),('D','2026-10-02'),
         ('E','2026-10-03')
),
events(user_id, event_day, event_type) AS (
  VALUES ('A','2026-10-08','action'),
         ('A','2026-10-08','action'),
         ('B','2026-10-07','action'),
         ('C','2026-10-09','action')
),
per_user AS (
  SELECT u.user_id, u.signup_day,
    CASE WHEN EXISTS (
      SELECT 1 FROM events e
      WHERE e.user_id = u.user_id
        AND e.event_type = 'action'
        AND e.event_day = date(u.signup_day, '+7 days')
    ) THEN 1 ELSE 0 END AS retained
  FROM users u
  WHERE date(u.signup_day, '+7 days') < '2026-10-10'
)
SELECT signup_day, COUNT(*) AS eligible_users,
       SUM(retained) AS retained_users,
       1.0 * SUM(retained) / COUNT(*) AS retention
FROM per_user
GROUP BY signup_day
ORDER BY signup_day;

EXISTS 只回答是否存在符合条件的事件，避免把重复事件算成多位用户。成熟条件与观察截止采用严格小于，和前面的排除定义一致。实际数据库还要按数据类型和业务时区处理。