SQL 面试：如何准确计算第 7 日用户留存率？

题干与适用场景

现有两张 PostgreSQL 表：

CREATE TABLE users (
  user_id bigint PRIMARY KEY,
  signup_at timestamptz NOT NULL,
  acquisition_channel text
);

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  event_at timestamptz NOT NULL,
  event_name text NOT NULL
);

请统计注册日期在 2026 年 1 月 1 日至 1 月 31 日之间的用户，按注册日期和获客渠道输出精确的 第 7 日留存率。业务时区是 America/New_York。用户注册的本地日历日是第 0 日；只有在 signupday + 7 这个本地日历日内至少发生一次 coreaction_completed，才算第 7 日留存。 第 1 至第 6 日、第 8 日或更晚的事件都不满足这个定义。

结果包含 cohortday、acquisitionchannel、cohortsize、retainedusers 和 d7retentionrate。同一用户在目标日发生多次符合条件的事件，也只能给分子贡献一次。 完全没有回访的用户仍在分母中。空渠道统一归入 unknown。

假设 datacompletethrough = '2026-02-01 05:00:00+00'。它是一个不包含右边界的数据摄取 水位，对应纽约时间 2 月 1 日零点。只有某个注册 Cohort 的完整第 7 日都在水位之前，才能 参与统计。因此，1 月 24 日注册用户的第 7 日是 1 月 31 日，已经成熟；1 月 25 日注册用户的 第 7 日是 2 月 1 日，尚未成熟。

这道题适合数据分析师、分析工程师和产品分析岗位。核心难点是写聚合前先对齐 Cohort 粒度、 回访窗口、业务时区、数据完整性和分群口径；最后的除法反而很简单。

面试官考察点

第一，候选人是否会先确认“第 7 日留存”的定义。听起来相近的三种指标其实不同：恰好在第 7 日活跃、第 1 至第 7 日内任意一天活跃、从第 7 日起的任意时间活跃。SQL 即使语法完全正确， 也可能回答错指标。本题要求第一种。

第二，是否守住分母。分母是成熟注册 Cohort 中的所有合格用户。如果从事件表出发，或者把 回访事件内连接到注册用户，没有回访的用户会被悄悄删除，留存率因此虚高。正确顺序是先构建 Cohort，再通过左连接保留它。

第三，是否能控制数据粒度。最终结果是一行一个注册日期和渠道，留存标记则应是一行一个用户。 原始事件可能包含重试、重复上报，以及同一用户在目标日的多次有效操作。直接数事件行得到的 是操作量，不是留存用户数，所以分子必须在用户粒度去重。

第四，时间口径是否正确。本地日历日不一定等于固定的 24 小时；采用夏令时的命名时区还会改变 UTC 偏移。先得到本地注册日期，再用命名时区的相邻零点构造每个用户的目标日区间，才能直接表达 题意。用 eventat = signupat + INTERVAL '7 days' 判断的是经过时长，不是日历日留存。

最后，候选人是否具备生产判断。最近的 Cohort 必须拥有完整观察窗口；当前时间已经过去，并不 代表事件管道的数据已完整。高质量回答会使用数据水位，说明迟到事件如何重算，并根据查询频率 和数据量选择索引或用户日活明细表。

回答前需要澄清的问题

• 第 7 日是精确日、区间还是滚动定义？ 本文只统计注册后的第七个本地日期。“七日内”以及

“第 7 日及以后”需要不同的事件条件。

• 哪个事件代表留存？ 本题使用 coreactioncompleted。登录、浏览、购买或任意事件体现的

产品价值不同，不能随意替换。

• 一天按什么时区定义？ 本题统一使用 America/New_York 日历日。改用用户个人时区或 UTC，

会改变注册归属和回访窗口。

• Cohort 何时成熟？ 只有不包含右边界的数据水位达到或超过目标第 7 日之后的本地零点时，

该 Cohort 才可统计。

• 迟到事件怎样处理？ 水位推进或水位前的数据发生回补后，受影响的 Cohort 必须重算，已展示

的留存率不应被当作永远不变。

• 采用哪个渠道值？ 查询假设 users.acquisition_channel 是注册时记录且不可变的归因。若字段

会更新，应使用带版本的注册归因快照。

• Cohort 的聚合粒度是什么？ 本题按注册日与渠道分组。周 Cohort 的用户级逻辑相同，只需改变

最终分组键。

• 比率输出小数还是百分数？ 查询保留四位小数；0.3333 表示 33.33%。

30 秒回答框架

“我会先把精确第 7 日定义为：用户注册本地日期加七，并固定业务时区。第一步用本地零点对应的 UTC 边界构建 1 月注册 Cohort，保留每个用户的注册日和注册时渠道；第二步排除完整目标日尚未被 不包含右边界的数据水位覆盖的 Cohort；第三步在每个用户的本地第 7 日半开区间中查找目标事件， 并去重为一名用户一条留存记录；第四步把这个标记左连接回成熟 Cohort，再按日期和渠道统计分母、 分子与比率。我会专门测试重复事件、零回访、第 6 日与第 8 日、本地零点、夏令时和成熟边界。”

分步骤深入解答

先把统计参数显式写出来。cohort_end 不包含右边界，水位代表第一个尚未完整观测的时刻。半开 区间能避免零点事件被相邻两天重复计算，也便于多个连续日期区间拼接。

WITH params AS (
  SELECT
    'America/New_York'::text AS tz,
    DATE '2026-01-01' AS cohort_start,
    DATE '2026-02-01' AS cohort_end,
    TIMESTAMPTZ '2026-02-01 05:00:00+00' AS data_complete_through
),
cohort AS (
  SELECT
    u.user_id,
    COALESCE(u.acquisition_channel, 'unknown') AS acquisition_channel,
    (u.signup_at AT TIME ZONE p.tz)::date AS signup_day
  FROM users AS u
  CROSS JOIN params AS p
  WHERE u.signup_at >= (p.cohort_start::timestamp AT TIME ZONE p.tz)
    AND u.signup_at < (p.cohort_end::timestamp AT TIME ZONE p.tz)
),
mature_cohort AS (
  SELECT c.*
  FROM cohort AS c
  CROSS JOIN params AS p
  WHERE c.signup_day + 7
    < (p.data_complete_through AT TIME ZONE p.tz)::date
),
retained AS (
  SELECT DISTINCT c.user_id
  FROM mature_cohort AS c
  CROSS JOIN params AS p
  JOIN events AS e
    ON e.user_id = c.user_id
   AND e.event_name = 'core_action_completed'
   AND e.event_at >= ((c.signup_day + 7)::timestamp AT TIME ZONE p.tz)
   AND e.event_at < ((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
)
SELECT
  c.signup_day AS cohort_day,
  c.acquisition_channel,
  COUNT(*) AS cohort_size,
  COUNT(r.user_id) AS retained_users,
  ROUND(
    COUNT(r.user_id)::numeric / NULLIF(COUNT(*), 0),
    4
  ) AS d7_retention_rate
FROM mature_cohort AS c
LEFT JOIN retained AS r
  ON r.user_id = c.user_id
GROUP BY c.signup_day, c.acquisition_channel
ORDER BY c.signup_day, c.acquisition_channel;

cohort 先把 1 月在纽约时区的起止零点转换成 UTC 时刻，再与 timestamptz 字段比较。这样既 准确表达本地日期范围，又避免在 WHERE 中逐行转换 signup_at，普通的时间范围索引仍有机会 生效。

成熟判断用日期更直观。水位在纽约时区转换后是 2 月 1 日；目标日期必须严格早于 2 月 1 日，才 表示它的结束零点已被覆盖。1 月 24 日加七得到 1 月 31 日，可以进入；1 月 25 日加七得到 2 月 1 日，必须排除。若水位不是本地零点，而是当天中午，更稳妥的写法是直接比较目标日结束时刻：

((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
  <= p.data_complete_through

retained CTE 生成的是类似半连接的用户标记。即使事件生产端发生重试，或者用户在目标日完成 十次核心动作，DISTINCT 也保证每个用户最多一行。在当前表结构中，一个用户只属于一个注册 Cohort，因此按 user_id 连接即可。若同一个人可能多次重新注册，模型还需要稳定的注册周期 ID， 后续连接也必须使用这个 ID。

最终左连接保留成熟 Cohort 的全部用户。COUNT(*) 是分母，COUNT(r.user_id) 只统计成功匹配的 留存标记。NULLIF 对空分组做防御；虽然从 mature_cohort 产生的实际分组至少有一行，但写清 除零行为有助于复用时不引入意外。

用一个小样本校验：1 月 1 日 organic Cohort 有 3 名用户，只有 1 人在目标日完成核心动作，结果 应为 3、1、0.3333；1 月 2 日 paid Cohort 有 2 人且都回访，结果应为 2、2、 1.0000。1 月 25 日注册用户无论后来是否有事件，都因水位下尚未成熟而不出现在结果中。

汇总总留存率时不能直接平均各行比率。1 人的日 Cohort 不应与 1000 人的 Cohort 拥有相同权重。 正确公式是：

overall_retention = SUM(retained_users) / SUM(cohort_size)

生产数据量较大时，可建立与选择条件和连接键相符的索引：

CREATE INDEX users_signup_at_idx
  ON users (signup_at);

CREATE INDEX events_core_action_user_time_idx
  ON events (user_id, event_at)
  WHERE event_name = 'core_action_completed';

只有当目标事件定义稳定、查询价值足以覆盖写入和存储成本时，部分索引才合适。若超大事件流需要 频繁刷新看板，可以增量维护一张以 (userid, activityday, event_name) 为唯一键的日活明细表， 从而避免每次扫描原始事件。activity_day 必须沿用同一个命名时区口径，否则优化会悄悄改变指标。

设合格用户数为 U，连接实际检查的相关事件行为 E。Cohort 范围扫描与用户数线性相关，留存 去重通常需要对候选记录做哈希或排序，最终聚合与成熟用户数线性相关。真实成本取决于事件选择性、 索引、统计信息和执行计划，因此应在代表性数据上查看 EXPLAIN (ANALYZE, BUFFERS)，不能只根据 SQL 文本承诺一个固定复杂度。

高质量示范回答

“写 SQL 前，我会固定四个口径：精确第 7 日、以 coreactioncompleted 作为回访事件、按纽约 日历日计算，以及使用不包含右边界的完整性水位。分母是所有完整第 7 日已经被观测的 1 月注册 用户；分子是其中至少有一次目标事件的去重用户。

我会先构建 Cohort。把 1 月在业务时区的起止零点转换成 UTC 时刻，用它过滤 signup_at，然后 保留每名用户的本地注册日和注册时渠道。成熟度单独放在一个 CTE 中，便于审计：在本题 2 月 1 日 零点的水位下，1 月 24 日是最后一个可以纳入的注册日期。

对每名成熟用户，我按 user_id、目标事件名和目标日半开区间连接事件。区间从 signupday + 7 的本地零点开始，到 signupday + 8 的本地零点之前结束。这两个边界由命名 时区计算，所以夏令时不会把日历规则变成固定 168 小时。随后对用户 ID 去重，把留存标记左连接 回 Cohort，再按注册日与渠道聚合。左连接很关键，因为没有回访的用户仍然属于分母。

验证时我会逐个检查 CTE：Cohort 必须一名 1 月注册用户一行；成熟 Cohort 在当前水位下必须停在 1 月 24 日；留存 CTE 的用户 ID 必须唯一；最终每组都满足 0 <= retainedusers <= cohortsize。测试数据要覆盖重复事件、零回访、错误事件名、第 6 日与 第 8 日、本地零点两侧、夏令时日期、空渠道和迟到回补。跨组总留存率用分子总和除以分母总和， 不能平均各组比率。”

常见错误

• 使用内连接。 零回访用户被删除，留存率会虚高。应先建 Cohort，再左连接用户级留存标记。
• 数事件而不是数用户。 COUNT(e.event_id) 可能大于 Cohort 人数。分子要按用户去重，或使用

布尔存在性判断。

• 没有说清第 7 日。 覆盖第 1 至第 7 日或第 7 日之后的条件都在计算其他指标。先用自然语言

写明区间，再写 SQL。

• 把注册时刻加 168 小时。 这是经过时长留存，不是命名时区下的日历日留存，在夏令时变化

附近可能产生不同结果。

• 在过滤条件中转换被索引时间列。 逐行把 signup_at 转成日期可能阻碍范围扫描。应把本地

常量边界转换为时刻。

• 纳入未成熟 Cohort。 新用户还没有完整回访机会，会制造虚假的低留存。应使用数据管道水位，

不能只看当前时间。

• 把水位当成永久真相。 回补会改写已统计 Cohort。必须定义重算和数据新鲜度规则。
• 读取会变化的渠道字段。 当前归因可能把未来信息泄漏到历史 Cohort。应使用不可变注册字段或

带版本快照。

• 平均分组百分比。 Cohort 大小时不相等，无权平均会扭曲总值。应先汇总人数再相除。
• 忽略空值和缺数语义。 空渠道需明确归类；发布指标前也要判断没有事件究竟代表零活跃，还是

管道数据不完整。

追问及应对

如何计算第 1 至第 7 日内的留存？

保留相同的 Cohort 和成熟判断，把事件区间改为从 signup_day + 1 的本地零点开始，到 signup_day + 8 的本地零点之前结束。成熟度仍要求整个第 7 日完整。还要明确第 0 日是否计入， 因为不同团队和分析工具可能采用不同规则。

如何计算“第 7 日及以后”的留存？

下界仍是 signup_day + 7 的本地零点，上界则改为报表截止时间。这个指标是累计型的，会受到 观察时长影响：老 Cohort 拥有更多回访机会。因此只能在相同 Cohort 年龄下比较，或者展示完整 留存曲线，不能发布一个没有封顶窗口的数字后直接横向比较。

PostgreSQL 能否不使用单独的 retained CTE？

可以。横向 EXISTS 查询或设计得当的聚合都能为每名 Cohort 用户生成一个布尔值。也可直接连接 后使用 COUNT(DISTINCT e.user_id) FILTER (WHERE ...)，但聚合前可能产生大量事件行。单独 CTE 能把粒度和正确性证明写得更清楚；最终方案应由实测执行计划决定。

如果每名用户使用不同的报告时区怎么办？

需要保存注册周期适用的时区，并用同一个值推导 Cohort 日期和目标日边界。用户历史时区变化还 需要明确政策。个人时区也意味着同一个日历 Cohort 标签会跨越不同 UTC 区间，预聚合数据必须 保留适用时区，或者直接保存已经规范化的本地日期。

怎样测试成熟边界？

在当前水位下分别创建 1 月 24 日和 1 月 25 日的注册用户，并都添加合法目标日事件。1 月 24 日 必须出现，1 月 25 日必须缺席。再把水位设为目标日结束零点之前一秒和恰好等于零点，确认不包含 右边界的规则没有多收或少收。

迟到事件应该怎样处理？

对外公布事件时间水位，并重算所有合格事件区间与回补范围重叠的 Cohort。如果摄取延迟有明确 服务目标，可以在名义第 7 日结束后增加安全延迟。保留原始人数与指标版本，确保每次修正可追踪。

生产环境应监控哪些不变量？

检查每组 cohort_size 都大于零、留存人数介于零和 Cohort 人数之间、不存在未成熟日期、Cohort 总人数能与注册源对账，并且事件最大时间确实覆盖所声明的水位。事件量异常和摄取延迟要单独告警， 避免把数据管道缺口误判为留存下降。