题目与适用场景
分析表为 events(eventid, userid, eventat, eventname, isinternaluser)，其中 eventat 是 PostgreSQL 的 timestamptz。用户至少发生一次 appopen、 viewdashboard 或 runreport 才算活跃，内部用户不计入。
按 America/New_York 日历日返回 2026 年 6 月 1 日至 6 月 30 日的每一天。报告日的 rolling7dactive_users 是当天或此前六个日历日内活跃过的不同合格用户数。因此 6 月 1 日 需要读取 5 月 26 日至 6 月 1 日；一个用户在三天产生二十条事件，窗口内仍只计一次。
查询必须保留零用户日期，明确时间边界，并说明迟到事件如何改变已发布结果。核心是滚动集合的 去重并集，不能把已经汇总的每日人数再做滚动求和。
面试官考察什么
第一是先定义指标再写语法。强回答会说明合格事件、排除人群、报告时区、输出粒度、包含首尾的 七个日期窗口和数据完整性边界。缺少这些定义时，两条语法正确的 SQL 可能回答不同问题。
第二是粒度控制。原始事件先变成唯一的 (userid, activitydate)。这一步消除同日重复，但 有意保留用户在不同日期的记录。最后一个窗口再统计这些用户集合的去重并集。
第三是能否拒绝看似方便的捷径。七个 DAU 相加会让同一用户按活跃天数重复计数。 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 表示七行，不保证是七个日历日；即使作用于完整 每日人数，它也无法恢复跨日用户并集。
最后是生产判断：请求范围之前要多扫描六天，用日期脊柱保留空日期，按声明时区转换时间戳，定义 迟到事件的刷新语义，并有意识地选择精确或近似扩展方案。
回答前需要澄清的问题
- 什么事件算活跃？ 只登录与发生有意义产品行为得到的集合不同。事件名、机器人和内部用户
排除都属于指标契约。
- 哪个时区定义一天？ 本题使用
America/New_York。UTC 会把本地午夜附近的事件放到另一日。 - 窗口是七个日历日还是连续 168 小时？ 本题要求本地日历日。夏令时切换附近的七天可能只有
167 或 169 个小时。
- 两端是否包含？ 用户集合覆盖
reportdate - 6到reportdate。源时间戳过滤使用左闭
右开区间，避免把下一次午夜重复计入。
- 没有事件的日期是否也要输出？ 要。应生成三十个报告日期，不能只从事件表取日期。
- 事件表何时完整？ 如果事件最多迟到三天，近期结果只能标记为暂定，或附带水位。SQL 无法
把不完整输入变成最终结果。
- 必须精确去重吗？ 面试题要求精确。只有误差契约得到认可后，超大规模才可使用可合并的
近似集合表示。
- 数据库和数据量是什么？ 本文使用 PostgreSQL。数仓可以替换日期生成或位图函数，但集合
语义不变。
30 秒回答框架
“我先定义活跃事件、排除人群、America/New_York 日期边界和每天一行的输出粒度。6 月 1 日 需要此前六天，所以源数据从 5 月 26 日开始扫描；把时间戳转为本地日期后，先去重成每用户每天 一行。然后用 generate_series 生成 6 月 1 日至 30 日的日期脊柱，让每个报告日左连接 date - 6 至当天的活跃记录，再统计不同用户。
我不会汇总 DAU，因为跨多日活跃的用户会重复；前六行窗口也会受缺失日期影响，而且不会创建 跨日去重并集。验证会覆盖午夜、重复事件、空日期和预热区间；迟到事件则通过水位标识或刷新近期 日期处理。”
分步骤深入解答
第一步：固定指标契约与输入范围
输出从 6 月 1 日开始，但源扫描从 5 月 26 日开始。只读取 6 月会低估前六个报告日。源上界为 7 月 1 日本地午夜，6 月 30 日的尾随窗口不需要未来事件。
先把这些本地午夜写成 timestamptz 常量，并直接过滤有索引的 event_at。如果在 WHERE 里把每个源时间戳都转成日期，普通范围索引可能无法有效裁剪。
第二步：规范成去重用户日
先用有界时间戳过滤，再把合格时刻转换为纽约日历日，最后按 user_id 和本地日期分组。一次事件 重试即使生成不同事件行，一个用户同日产生二十个事件，也都会缩成一个用户日。
这一步并未直接解决最终问题。同一用户若 6 月 1 日和 2 日都活跃，两条用户日仍要保留，才能让 不同报告日的尾随窗口正确包含该用户。
第三步：生成完整日期脊柱
generate_series 在没有事件时仍生成每个报告日。若从事件表取日期，空日期会消失，ROWS 窗口的行数也会偏离日历，而且看板上不会出现值为零的点。日期脊柱才是权威输出粒度。
第四步：统计每个窗口的去重并集
直接、精确的查询如下：
WITH params AS (
SELECT
DATE '2026-06-01' AS report_start,
DATE '2026-06-30' AS report_end
),
activity_days AS (
SELECT
e.user_id,
(e.event_at AT TIME ZONE 'America/New_York')::date AS activity_date
FROM events AS e
WHERE e.event_at >= TIMESTAMPTZ '2026-05-26 00:00:00 America/New_York'
AND e.event_at < TIMESTAMPTZ '2026-07-01 00:00:00 America/New_York'
AND e.event_name IN ('app_open', 'view_dashboard', 'run_report')
AND e.is_internal_user = false
GROUP BY
e.user_id,
(e.event_at AT TIME ZONE 'America/New_York')::date
),
report_dates AS (
SELECT gs::date AS report_date
FROM params AS p
CROSS JOIN generate_series(
p.report_start,
p.report_end,
INTERVAL '1 day'
) AS gs
)
SELECT
d.report_date,
COUNT(DISTINCT a.user_id) AS rolling_7d_active_users
FROM report_dates AS d
LEFT JOIN activity_days AS a
ON a.activity_date BETWEEN d.report_date - 6 AND d.report_date
GROUP BY d.report_date
ORDER BY d.report_date;左连接保留没有匹配活动的报告日，COUNT(DISTINCT a.user_id) 会忽略左连接产生的空值。区间 恰好包含七个 date：当天和此前六天。
第五步：证明常见窗口捷径为什么失败
假设用户 A 周一和周二都活跃，用户 B 只在周二活跃。两日 DAU 分别是 1 和 2，但两日用户并集 是 2，不是 3。每日人数一旦替换用户身份，SQL 就无法得知 A 在两天重复。
行窗口还有另一层错误。若周三无事件且输入中没有周三，“前六行”可能向前覆盖八个甚至更多 日历日。日期脊柱能修复日历缺口，但对 DAU 做滚动求和仍会重复用户。正确顺序是先求并集，再求 基数。
第六步：在不改变语义的前提下扩展
中等范围下，为原始数据建立 event_at 索引，并在 7 倍区间连接前缩减成用户日。按 (activitydate, userid) 存储物化日活明细，可避免反复扫描原始事件；分区裁剪必须包含预热日。
报告范围较长时，可把每个用户日展开到最多七个适用报告日，限制在请求范围内后再按用户去重。 连接形态会改变，但最坏仍是 7 倍展开。支持精确位图的引擎可以合并每日用户位图；近似 sketch 必须支持集合并集并公开实测误差。直接相加每日 HyperLogLog 估计值仍然错误，因为基数相加不能 消除交集。
第七步：定义迟到数据与验证
结果应附带 as_of 水位。如果管道允许事件迟到三天，至少要刷新所有七日输入窗口与可变数据 相交的报告日。稳定事件 ID 有助于摄取去重，用户日分组能抵抗同一用户的多条合格事件；两者都 不能替代完整性监控。
手工构造对照数据：重复事件、同一用户跨多日、内部用户、不合格事件、5 月 26 日和 31 日的预热 事件、空日期、本地午夜两侧的时刻，以及夏令时边界。用简单应用集合并集逐日对照。再在接近生产 的数据量上运行 EXPLAIN (ANALYZE, BUFFERS)，检查源裁剪、用户日基数、连接展开、耗时与落盘。
高质量示范回答
“我会先定义集合：合格用户至少产生一次允许的产品事件，排除内部用户，一天按 America/New_York 计算。对报告日 D，集合包含本地活动日期在 D 减六至 D 之间的所有合格用户， 首尾都包含；输出必须有三十行。
我会用左闭右开的本地午夜边界过滤 5 月 26 日至 7 月 1 日的原始时间戳，过滤后再转换本地日期， 按用户和日期分组。generate_series 生成 6 月 1 日至 30 日，每个日期左连接其尾随区间中的用户日， 再用 COUNT(DISTINCT user_id) 得到并集基数。
我会拒绝 SUM(DAU)，因为跨多日用户会重复；也拒绝 ROWS 6 PRECEDING，因为行不等于日历日， 而且每日汇总已经丢失身份。扩展时先物化 (activitydate, userid) 并裁剪预热范围；只有精确值 不再是要求时，才考虑精确位图并集或经过误差验证的近似集合并集。结果附水位，迟到数据触发有界 重算。”
常见错误
- 汇总七个 DAU → 重复用户会按活跃日期重复计数 → 在窗口中合并用户身份后再计数。
- 在稀疏日期上用
ROWS 6 PRECEDING→ 六行可能跨越超过六个此前日期 → **生成完整日期脊柱，
明确日历边界。**
- 只扫描 6 月 → 6 月初窗口丢失 5 月活动 → 加入六天预热区间。
- 在源过滤中转换
event_at→ 普通时间戳索引可能无法有效裁剪 → **先用左闭右开的
timestamptz 边界过滤，再派生本地日期。**
- 统计原始事件 → 重试与重复使用会放大人数 → 先去重用户日，最终窗口仍按用户去重。
- 从事件派生输出日期 → 空日期会消失 → 让日期脊柱决定输出粒度。
- 把近期数字称为最终值 → 迟到事件会改变集合 → 发布水位并刷新受影响窗口。
- 相加每日近似基数 → 基数相加无法消除重叠 → 先合并支持集合的 sketch 或位图，再估算并集。
追问及应对
追问一：如果指标定义为此前连续 168 小时，怎么改？
改为比较时刻，而不是本地 date。对每个报告时刻使用明确的半开或闭合时间戳区间，例如 (reportat - interval '168 hours', reportat]。夏令时切换附近，它与纽约七个日历日不同， 不能把两个定义混称。
追问二：窗口函数本身能完成精确滚动去重吗？
当聚合可以由行值组合时，窗口聚合很合适，例如滚动求和。本题需要维护集合，并在旧日期离开窗口 时删除成员。直接 PostgreSQL 解法保留用户身份，再连接报告日。专用引擎可能提供精确位图并集窗口， 但那是引擎能力，不等于可以汇总每日人数。
追问三：一条事件迟到三天时，刷新哪些日期？
先得到事件的本地活动日期 A。它可能影响 A 至 A 加六的报告日，再与已发布范围求交。只替换这些 分区，完成对账后推进水位，并保证重算幂等。只刷新 A 会漏掉后续六个窗口。
追问四：如果还要按国家分组呢？
先说明国家来自事件、用户当前资料，还是活动发生时的慢变资料。选择会改变历史事实。把选定国家 加入用户日粒度、日期脊柱组合、分组和验证对照。如果连接当前资料，用户搬家后历史可能被改写。
追问五：精确去重成本过高怎么办？
先测量精确查询。若获准的误差预算允许近似，可按活动日期与切片保存可合并集合 sketch，合并七个 sketch 后只估算一次。用低、中、高基数切片与精确集合比较偏差和相对误差。计费、资格等不能容忍 估算误差的决策仍保留精确处理。