SQL Interview Prep · 课时

每位用户的最长连续记录

Q: 「每位用户的最长连续记录」这节课中我会学到什么？

计算每个分组内连续记录的最大长度。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

计算每个分组内连续记录的最大长度。

每位用户的最长连续记录 是 CoddyKit 上的免费 SQL Interview Prep 课时。这是第 2 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。

问题

在检测连续日期之后，一个常见的追问是：“对于每位用户，其连续活跃天数中最长的一段是多少？” 产品和增长团队经常通过这个问题衡量用户参与度。

您已经知道如何识别每一段连续记录。新的步骤是找出每位用户的最大连续段长度，而且通常还要返回那段最长记录的日期。这节课将直接建立在间断与连续区间骨架之上。

回顾连续区间构建方法

在上一课中，每段记录使用 login_date - ROW_NUMBER() 作为连续区间锚点。每位用户可能有多个连续区间；我们会先为每个连续区间计算一行，然后再将结果缩减为每位用户一行。

请记住这个两层计划：先构建连续区间，再对这些连续区间进行聚合。

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

每个连续区间一行

将每个连续区间合并为一条汇总记录，并保留其长度和日期范围。按用户和锚点分组，然后计算各项指标。

我们将这个 CTE 命名为 islands，这样下一层就能清晰地从中读取数据。

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT * FROM islands;

简单答案：MAX 长度

如果面试官只需要长度，最后一步只需一行：按用户对连续区间分组，并取最大长度。

当不需要开始日期和结束日期时，这是最简洁的答案。

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

同时返回日期

面试官通常还会补充要求：“并显示这段连续记录发生的时间。” 单独使用 MAX 无法告诉您哪个连续区间胜出。您需要在每位用户内部对连续区间进行排名，并保留排名为 1 的记录。

使用按长度降序排列的 ROW_NUMBER，让每位用户的最长连续段获得排名 1。请添加一个并列时的次级排序条件，以确保结果具有确定性。

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

排名并筛选

将排名包装在一个 CTE 中，然后筛选出 rnk = 1。不能直接在 WHERE 中筛选窗口函数，因此额外的查询层是必需的。

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

并列时使用 RANK 还是 ROW_NUMBER

如果某位用户有两段长度相同且都是最长的连续记录，而面试官希望同时返回两段，请将 ROW_NUMBER 替换为 RANK，并保留 rnk = 1。

ROW_NUMBER — 每位用户恰好保留一个胜出者（除非添加次级排序条件，否则并列时的选择是任意的）。
RANK — 所有并列的最长连续段共享排名 1，并全部保留。

请先确认面试官需要哪种行为；这体现了您对边界情况的关注。

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

示例演练

假设用户 7 在 1 月 1 日至 4 日登录，随后在 1 月 10 日至 11 日登录，之后又在 1 月 20 日至 23 日登录。三个连续区间的长度分别为 4、2 和 4。最长长度为 4，并且存在并列。

使用 ROW_NUMBER 加上次级排序条件 streak_start：只返回 1 月 1 日至 4 日的连续段。
使用 RANK：返回 1 月 1 日至 4 日以及 1 月 20 日至 23 日的两段连续记录。

将这一点明确说出来，可以证明您考虑了重复记录的情况。

处理没有登录的用户

面试官可能会问：“从未登录过的用户怎么办？” 这些用户在 logins 中没有任何行，因此会从结果中消失。如果必须让他们以连续天数为 0 出现，请将完整的 users 表 LEFT JOIN 进来，并使用 COALESCE。

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

性能说明

此模式只需对数据进行一次有序遍历，再进行一次分组。为了保持查询速度：

确保存在 (user_id, login_date) 索引，这样窗口中的 ORDER BY 就无需额外排序。
如果来源每天有多个事件，请尽早去重。
避免在 ORDER BY 中对 login_date 使用函数，否则可能阻止使用索引。

对于非常大的表，这种方法的性能会明显优于任何自连接方案。

完整的面试回答

下面是返回每位用户最长连续天数及其日期的完整、润色后的查询 — 这是应当写在白板上的版本。

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

快速检查

请选择适合该需求的工具。

回顾

要计算每位用户的最长连续天数：

使用 login_date - ROW_NUMBER() 锚点构建连续区段。
将每个连续区段汇总为长度及日期范围。
只需要长度时，按用户分组计算 MAX(streak_len)。
如果还需要日期，就按用户为连续区段排名，并保留排名为 1 的区段 — 使用 RANK 可包含并列结果，使用 ROW_NUMBER 则只保留一个结果。
对 users 使用 LEFT JOIN，以显示连续天数为 0 的用户。

接下来：检测满足某个条件的 N 个连续行。

常见问题解答

「每位用户的最长连续记录」课时是免费的吗？

是的 — 「每位用户的最长连续记录」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。

「每位用户的最长连续记录」这节课中我会学到什么？

计算每个分组内连续记录的最大长度。你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 2 节课，共 4 节。

「每位用户的最长连续记录」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Interview Prep 课中编写并运行代码吗？

能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Interview Prep