题目与适用场景
现有一张 PostgreSQL 事件表：
CREATE TABLE user_logins (
user_id bigint NOT NULL,
login_at timestamptz NOT NULL
);对每个用户，找出其在 America/New_York 当地自然日内至少登录一次的最长连续日期区间， 返回 userid、streakstart、streakend 和 streakdays。同一当地日期内的多次事件只算 一个活跃日；缺少任一当地日期就中断区间；如果某用户有两个长度相同的最长区间，两条都要 返回。没有事件的用户不出现在结果中。
这是一道数据与 SQL 面试题，计算的不是连续若干个 24 小时。夏令时切换附近的当地自然日 可能只有 23 小时或长达 25 小时，但依然只是一张日历上的一天。因此，题目要求先固定报表 时区，再把时间戳转换成日期。主答案使用 PostgreSQL，其他数据库方言的日期运算写法会有所 不同。
核心是 gaps and islands 问题：对已排序的日期做变换，让同一段连续日期共享一个稳定键，按 该键聚合出每个连续区间，最后为每个用户保留所有并列最长的区间。
面试官在考察什么
第一个信号是候选人能否在写窗口函数前先确定粒度。原始数据一行代表一次登录事件，业务 粒度却是“每个用户、每个当地自然日一行”。如果省略这一步，同一天的重复事件会错误推进 ROW_NUMBER()，也会放大计数并改变区间边界。
第二个信号是能否推导连续区间的分组键。去重后的日期排好序后，在一段连续日期内，日期和 ROW_NUMBER() 都逐行增加 1。因此，用日期减去行号对应的天数偏移，同一连续区间内会得到 相同值。遇到缺口时，日期增加超过 1 天，行号仍只增加 1，分组键随之改变。
第三个信号是对输出契约的把握。“最长连续登录”在存在等长区间时有歧义。用 ROW_NUMBER() 只挑一条，会无声丢掉合法并列结果。本题明确要求返回全部并列最长区间， 因此需要把每个区间长度和该用户的最大区间长度比较。
第四个信号是时区正确性。直接把 login_at 转为 date 会采用数据库会话时区，不同环境 可能得到不同答案。正确做法是先把 timestamptz 转到指定业务时区，再取日期。对于会随 夏令时改变偏移量的地区，固定 UTC 偏移量不够用。
最后还要看验证和规模判断。正确查询应当逐层检查每个 CTE，并覆盖同日重复事件、单日区间、 日期缺口、并列最长、当地午夜和夏令时边界。若要反复查询一张很大的事件表，把原始事件预先 缩减为每个用户每天一行，通常比微调最后的窗口查询更有价值。
回答前要先确认的问题
- 一天按什么定义？ 命名业务时区、UTC 或每个用户自己的时区会改变日期转换，甚至改变
最终答案。本题统一使用 America/New_York。
- 同一天登录多次是否重复计数？ 本题不重复计数，所以必须在编号前去重。若指标计算的是
连续事件，粒度和分组规则都会改变。
- 少一天是否必然中断？ 是。若题目改为 30 分钟无活动才划分新会话，就应比较前一行，
不能再用严格的自然日相邻关系。
- 并列结果怎么处理？ 本题返回全部并列最长区间。如果只取最近区间，需要另行明确
破同分规则。
- 时间范围是否有限制？ 日期筛选可以减少工作量，但也可能截断从范围起点之前开始的
连续区间。调用方必须说明要的是“范围内连续区间”还是跨越边界的完整区间。
userid或loginat是否可能为空？ 当前表结构规定不为空。如果允许空值，就要在
排序和分组前约定处理方式。
- 这是一次性查询还是长期产品指标？ 临时分析可以直接扫描并排序每日数据。持续刷新的
看板可能值得维护一张增量更新的用户日表。
30 秒回答框架
“我会先按约定的业务时区转换 timestamptz，并去重成每个用户、每个当地日期一行。然后在 每个用户内部按日期排序并生成 ROWNUMBER()。对严格连续的日期，loginday - 行号 × 1 天 在同一连续区间内保持不变，遇到缺口就会改变，因此可以按这个键聚合出区间起止和长度。接着 把每个区间长度与该用户的最大长度比较，这样不会丢掉并列结果。我会测试同日重复事件、单日 区间、日期缺口、并列最长、当地午夜和夏令时边界，并在代表性数据上检查执行计划。”
分步深入分析
先把事件流规范到业务粒度。对 timestamptz 使用命名时区执行 AT TIME ZONE，得到该时区的 墙上时间；再转成 date，得到业务自然日。SELECT DISTINCT 随后保证每个用户每天恰好一行。
完整查询如下：
WITH login_days AS (
SELECT DISTINCT
user_id,
(login_at AT TIME ZONE 'America/New_York')::date AS login_day
FROM user_logins
),
numbered AS (
SELECT
user_id,
login_day,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_day
) AS rn
FROM login_days
),
grouped AS (
SELECT
user_id,
login_day,
login_day - (rn * INTERVAL '1 day') AS island_key
FROM numbered
),
streaks AS (
SELECT
user_id,
MIN(login_day) AS streak_start,
MAX(login_day) AS streak_end,
COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, island_key
),
scored AS (
SELECT
user_id,
streak_start,
streak_end,
streak_days,
MAX(streak_days) OVER (PARTITION BY user_id) AS max_streak_days
FROM streaks
)
SELECT
user_id,
streak_start,
streak_end,
streak_days
FROM scored
WHERE streak_days = max_streak_days
ORDER BY user_id, streak_start;证明可以直接沿着每日有序数据展开。对某个用户，把去重日期记为 d1, d2, ...，对应行号为 1, 2, ...。若 d(i+1) = d(i) + 1 天，下一行日期与行号各多 1，减去行号天数偏移后得到的 分组键不变。若中间至少缺少一天，d(i+1) 会增加 2 天或更多，而行号仍只增加 1，分组键变大， 新的区间由此开始。由于已经去重，COUNT(*) 等于自然日数量，MIN 和 MAX 则正好是区间边界。
最后一步特意使用窗口 MAX，没有再用 ROW_NUMBER()。只要区间长度等于该用户的最大长度， 它就会被保留。如果产品后来只想要一条记录，应先写清楚“结束日期最近者优先”之类的规则， 再采用确定性排序；不能在 SQL 里擅自补出这一规则。
假设两个用户规范化后的日期是：
user 1: Mar 07, Mar 08, Mar 09, Mar 11, Mar 12
user 2: Nov 01, Nov 02, Nov 04, Nov 05
result:
user 1 | Mar 07 | Mar 09 | 3
user 2 | Nov 01 | Nov 02 | 2
user 2 | Nov 04 | Nov 05 | 2用户 1 的最大区间为 3 天。用户 2 有两个独立的 2 天最大区间，所以必须返回两条。同一显示 日期上无论有多少原始事件，结果都不变。夏令时切换附近，应判断当地日期是否相邻，不能判断 两个时间戳是否恰好相隔 24 小时。
设原始事件数为 N，去重后的用户日记录数为 D。去重需要读取 N 行，可能使用哈希或排序； 窗口步骤最多按用户和日期排序 D 行。面试中可给出一个实用的排序计划上界：时间复杂度 O(N log N + D log D)，中间空间 O(D)，同时说明优化器也可能使用哈希、已有顺序、并行执行 或磁盘溢写。生产上能否接受，最终要看执行计划，不能只看 Big-O。
如果要反复在数十亿事件上计算指标，可以增量维护一张以 (userid, loginday) 为唯一键的表。 这样，时区转换和同日去重会移到摄取或批处理边界，连续区间查询只需读取 D 条每日记录， 无需重复扫描 N 条事件。如果临时查询限定了时间范围，应先用可使用索引的 UTC 时间戳边界 筛选，再转为当地日期；这些 UTC 边界要由命名时区的当地午夜推导，才能正确处理夏令时变化。
不要只看最终结果，把中间层也检查出来：
-- These checks are run against the corresponding CTE or materialized test result.
SELECT user_id, login_day, COUNT(*)
FROM login_days
GROUP BY user_id, login_day
HAVING COUNT(*) > 1;
SELECT *
FROM numbered
ORDER BY user_id, login_day;
SELECT *
FROM streaks
WHERE streak_days <> (streak_end - streak_start + 1);第一条和第三条检查都应返回零行。numbered 的输出能直接暴露粒度或排序错误。把完整查询前缀 改为 EXPLAIN (ANALYZE, BUFFERS)，可以看到扫描、排序、行数估计、临时 I/O，以及是否需要更早 缩减原始事件。如果直接执行生产语句成本过高，应在安全且有代表性的数据副本上运行。
日期平移法并非通用解。如果超过 30 分钟才划分新会话，或状态值变化才开启新区间，应使用 LAG() 查看前一行，标记每个边界，再对边界标记做累计 SUM()。选择规则很直接：严格按固定 单位连续时用平移键；连续性取决于自定义比较时用边界标记。
高质量示范回答
“写 SQL 前，我会先确定粒度和并列结果契约。原表每个用户可能有多条事件，但指标按每个用户 在 America/New_York 的一个自然日计一次。因此，我会先把 timestamptz 转成该命名时区， 再取 date 并去重，然后才使用窗口函数。这样也能避免会话时区暗中改变答案。
处理 gaps and islands 时，我会在每个用户内部按当地日期排序并生成 ROW_NUMBER()。连续区间 内，日期和行号都逐行加 1，所以用日期减去行号对应的天数偏移会得到一个常量。若缺少日期， 日期的跳幅会大于行号，分组键随之改变。按用户和这个键聚合，即可得到每段连续区间的开始、 结束和活跃日数。
接着我会对区间长度计算窗口最大值，并保留与最大值相等的所有区间。这样符合题目要求，不会 无声丢掉并列最长记录。排序型计划的上界大约是 O(N log N + D log D)，其中 N 为原始事件数， D 为去重用户日数；实际是否溢写仍要看执行计划。
测试数据会覆盖同日多次事件、只有一天的用户、内部日期缺口、两个并列最大区间、当地午夜两侧 的事件和夏令时切换。对长期运行的大规模指标，我会维护唯一用户日表，在更小的粒度上执行窗口 逻辑。如果连续定义从自然日相邻改为时间阈值，我会换成 LAG()、边界标记和累计和。”
常见错误
- 直接给原始事件编号 → 同日重复事件会推进行号并放大计数 → **先去重成每个用户每天一行，
再使用窗口函数。**
- 直接把
timestamptz转成date→ 答案依赖会话时区 → 先转换到指定业务时区。 - 使用固定 UTC 偏移量 → 命名时区改变偏移量时，当地日期会算错 → **使用带日历规则的 IANA
时区。**
- 比较时间戳是否相隔 24 小时 → 23 小时或 25 小时的当地自然日会中断合法区间 → **按契约
比较当地日期是否相邻。**
- 只按平移后的日期分组 → 分组键相同的不同用户会被合并 → **同时按
user_id和
island_key 分组。**
- 用
ROW_NUMBER()只取一条 → 并列最长区间会被丢弃 → 把每个区间与该用户最大值比较。 - 没有边界规则就筛选报表区间 → 跨越起始日的区间被截断并可能被误标 → **明确结果是范围内
区间还是完整区间。**
- 使用
LAG()时不处理首行 → 第一段区间没有起点 → 把前一行为空视为新分组开始。 - 只说 Big-O → 排序溢写或基数估算错误仍然不可见 → **检查中间行数和
EXPLAIN (ANALYZE, BUFFERS)。**
- 每次刷新看板都扫描完整事件历史 → 重复的时区转换与去重占据主要成本 → **工作负载足够大时
维护唯一用户日粒度。**
追问与回答
追问 1：如果只返回最近的一段最长区间，怎么改？
连续区间的构造不变。得到 streaks 后，按用户依次使用 streakdays DESC、streakend DESC， 最后用 streak_start DESC 做确定性的最终破同分排序，只返回排名第一。要明确说明输出契约已经 改变：等长区间不再全部保留。
追问 2：如果 30 分钟没有活动就结束一次会话，怎么改？
日期平移无法表达这种连续性。按用户和时间戳排序，使用 LAG(login_at) 获取前一条记录；首行 或间隔大于 30 分钟时标记为新会话，再用显式的 ROWS UNBOUNDED PRECEDING 窗口帧对标记做 累计 SUM。最后按用户和生成的会话 ID 聚合。
追问 3：如果每个用户都有自己的时区，怎么处理？
把事件关联到在事件发生时有效的版本化用户时区，再取日期。仅使用用户当前资料中的时区，用户 搬家后会重写历史日期。需要先确认产品希望历史活动固定在当时的时区，还是按用户当前时区重新 计算；这是两个不同指标。
追问 4：如果只查最近 90 个当地自然日，怎么做？
先明确区间是否允许从窗口之前开始。若只要窗口内结果，根据命名时区的起止当地午夜推导两个 UTC 时刻，用它们筛选 login_at，再做规范化。若要完整区间，就必须额外读取足够早的每日记录， 直到找到真正缺口；直接截取 90 天无法证明真实起点。
追问 5：如何支持每日看板的高效查询？
维护 userlogindays(userid, loginday)，设置唯一键并使用幂等 upsert。事件流水按统一时区规则 更新该表。只重算每日记录发生变化的用户，或者定期从带重叠的时间窗口重建，以吸收迟到事件。 发布指标前，把每日行数与原始数据源做对账。
追问 6：上线前必须有哪些测试？
使用表驱动用例覆盖重复事件、单行用户、内部缺口、并列最长、当地午夜、夏令时开始和结束、 迟到事件以及报表边界穿越。断言每日记录唯一，每段区间都满足 streakdays = streakend - streak_start + 1，且所有返回区间都等于该用户最大值。抽样用户要把 增量每日表与原始事件重算结果比较，最后在接近生产的基数上检查查询计划和临时 I/O。