SQL Interview Prep · 课时

按首次操作定义用户群组

Q: 「按首次操作定义用户群组」这节课中我会学到什么？

根据每位用户的首次事件日期为其分配用户群组。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

根据每位用户的首次事件日期为其分配用户群组。

按首次操作定义用户群组 是 CoddyKit 上的免费 SQL Interview Prep 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。

面试中为何会考察队列

当产品分析面试官说“构建一个队列”时，考察的是您能否根据用户首次完成某件事的时间将每位用户分配到一个群组，然后随时间跟踪该群组。

队列是一组在同一时间段内共享某个起始事件的用户，通常是首次购买、注册或登录。队列的价值在于，它能让您在相同的基准上比较用户：一月队列中的每个人，都是从自己的一月起点开始衡量的。

本课重点练习的第一个子技能，也是最基础的子技能，是可靠地计算每位用户的首次行为日期。

源表

几乎所有队列问题都从一张事件表开始：每个用户行为对应一行，并带有时间戳。请设想一张 events 表：

user_id — 谁执行了行为
event_type — 执行了什么行为
event_at — 行为发生的时间，以时间戳表示

面试时，请明确说出数据粒度：“这是一条事件一行吗？同一个用户可以出现多次吗？”答案几乎总是可以，这正是您需要通过聚合将数据压缩为每位用户的首次行为的原因。

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

首次行为 = 时间戳的 MIN

核心操作很简单：按 user_id 分组，并取 MIN(event_at)。这个最小值就是用户的首次行为，也就是将用户分入某个队列的时刻。

这是面试官最希望先听到的答案，之后再考虑复杂的窗口函数。普通的 GROUP BY 就是正确、易读且高效的方案。

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

筛选定义事件

队列通常由某个特定行为定义，而不是由任意事件定义。“按用户的首次购买划分队列”意味着您必须在取最小值之前先筛选出购买记录。

请将筛选条件放在 WHERE 中，这样 MIN 只会处理符合条件的记录。一个常见的面试陷阱是先对所有事件取 MIN，再在之后进行筛选；这样会让那些购买前浏览过产品的用户被分配到错误的起始日期。

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

将数据归入队列周期

队列通常表示一个周期，而不是一个精确的时间戳，例如“2024-03 队列”或“2024-03-04 所在周”。请将首次行为日期截断到相应的周期粒度。

在 Postgres 中使用 DATE_TRUNC('month', ...)。在 MySQL 中可以使用 DATE_FORMAT(d, '%Y-%m-01')；在 SQL Server 中可以使用 DATETRUNC(month, d) 或计算月份第一天。在面试中说明您使用的方言，这样语法选择就显得经过了有意考虑。

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

封装到 CTE 中

每位用户的队列分配是留存查询中会反复使用的构件，因此请将它封装到一个名称清晰的 CTE 中。这样可以让后续步骤更易读，也能向面试官展示您会用可组合的部分来构建查询。

从这里开始，所有后续查询都可以连接回 user_cohort，以确定用户属于哪个群组。

WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT * FROM user_cohort;

队列规模：统计成员数

面试官期望您进行的第一个合理性检查是队列规模：每个队列中有多少用户。请按 cohort_month 对分配结果的 CTE 分组，并统计不重复的用户数。

即使该 CTE 已经做到每位用户一行，也建议出于稳妥使用 COUNT(DISTINCT user_id)；这表明您考虑到了数据粒度。这个计数会成为之后计算所有留存百分比的分母。

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

窗口函数替代方案

面试官有时会要求为每一行事件记录附加队列标签，而不是生成一张汇总表。这时窗口函数就很有用：MIN(event_at) OVER (PARTITION BY user_id) 可以在不删除记录的情况下计算首次行为。

当您需要在一次处理过程中同时保留详细事件和队列标签时，这种方法非常方便，也是进行留存统计的基础。

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

同值与重复项陷阱

如果某位用户有两个事件恰好发生在同一个最早时间戳，怎么办？MIN 可以干净地处理这种情况：无论有多少行并列，它都只返回同一个最小值，因此每位用户仍然只会被分配到一个队列。

相比之下，使用 ROW_NUMBER() ... ORDER BY event_at 时，并列记录会被任意打破；您必须添加一个确定性的次级排序条件（例如 event_id）才能获得稳定结果。主动提到这种取舍，会显得您经验丰富。

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

时区与日期边界

一个细微的面试考察点是：纽约晚上 11:30 发生的购买，在 UTC 中已经是第二天。如果按日历日划分队列，时区会决定用户被分入哪个队列。

稳妥的回答是：将时间戳以 UTC 存储，然后在截断之前转换为业务时区。请明确说明指标中的“日”由哪个时区定义，因为这个单一决定就可能让成千上万的用户在不同队列之间移动。

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

排除时间窗口前的用户

真实分析通常会将队列限制在某个日期范围内，例如“在第一季度开始的队列”。请筛选聚合后的首次行为日期，这意味着应使用 HAVING 子句或对 CTE 进行外层筛选，而不是对原始事件使用 WHERE。

按日期筛选原始事件会产生错误：某位用户可能在十二月首次购买，但也在第一季度有过行为，这样他就会被错误地混入第一季度队列。请始终根据计算出的首次行为进行限制。

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

快速检查

面试官问：“按每位用户的首次购买月份划分队列。用户可能在购买前浏览。”哪种方法是正确的？

回顾：定义队列

关于队列定义面试题，请牢记以下要点：

队列按照用户的首次符合条件的行为对用户进行分组。
在 WHERE 中筛选定义事件后，使用 MIN(event_at) 计算首次行为。
使用 DATE_TRUNC（或相应方言的等价语法）将其归入某个周期。
将分配逻辑封装在 CTE 中以便复用；COUNT(DISTINCT user_id) 可以得到队列规模。
注意时区的日期边界，并根据计算出的首次行为限制日期范围，绝不要根据原始事件进行限制。

掌握这些内容后，下一课的留存矩阵就只是一个连接查询。

常见问题解答

「按首次操作定义用户群组」课时是免费的吗？

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

「按首次操作定义用户群组」这节课中我会学到什么？

根据每位用户的首次事件日期为其分配用户群组。你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

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

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

「按首次操作定义用户群组」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 SQL Interview Prep