题目与适用场景
有一张 PostgreSQL 事件表：
CREATE TABLE events (
user_id bigint NOT NULL,
event_id bigint PRIMARY KEY,
event_name text NOT NULL,
event_time timestamptz NOT NULL
);请计算三步漏斗：visit → signup → purchase。只要用户在半开报告区间 [start_at, end_at) 内至少发生一次 visit，就进入队列；区间内最早的一次访问是漏斗锚点。 合格的注册是锚点之后最早的注册，合格的购买是被选中注册之后最早的购买。两步都不得晚于 锚点访问后的 24 小时。
event_id 全局唯一。事件生产方保证：同一用户的两个事件时间相同时，较小的 event_id 先发生。 恰好在锚点后 24 小时发生的购买计入，晚一微秒也不计入。漏斗步骤之间允许夹杂其他事件；同一步骤 重复发生，也不能让一个用户被重复计数。
返回一行：visited_users、signed_up_users、purchased_users、signup_rate 和 purchase_rate。两个转化率都以访问队列为分母，返回四位小数的比例值。空队列的三个计数为 0， 两个转化率为 null。
这道题适用于数据分析师、分析工程师和产品分析岗位。它考察的不只是条件聚合，还包括队列粒度、 事件顺序、重复事件下的有效链选择、时间边界，以及如何证明查询不会把不可能的用户路径算成转化。
面试官在考察什么
第一个信号是指标口径意识。“三个事件都出现过”还不足以定义漏斗。候选人需要问清楚哪次访问是 锚点、步骤是否必须有序、窗口是在访问后 24 小时关闭还是每一步重新计时，以及转化率以前一步还是 初始队列为分母。任何一个选择变化，结果都会变化。
第二个信号是粒度控制。原表是一行一个事件，输出统计的是用户。队列必须至多一行一个用户，后续 每次查找也必须至多为这行队列选择一个事件。先做宽表连接再用 COUNT(DISTINCT user_id)，可能只是 掩盖错误的事件组合，并没有证明事件链有效。
第三个信号是顺序推理。彼此独立的 MIN(CASE WHEN event_name = 'purchase' ...) 不一定会找到被选中注册之后最早的购买。用户可能先 购买、再注册、随后再次购买；有效购买是第二次。每一步都必须依赖上一步实际选中的事件。
第四个信号是确定性的时间语义。仅靠时间戳无法排列同一时刻的事件。本题明确给出 event_id 作为 平局裁决，因此比较键是 (event_time, event_id)。如果数据源没有这项保证，SQL 无法判断同一时刻 谁先发生，也不应自行编造因果顺序。
最后一个信号是生产判断。漏斗窗口会延伸到 end_at 之后，因此还需要读取更晚的事件。只有数据管道 已完整处理到 end_at + 24 小时，最新队列才成熟。高质量回答还会讨论迟到数据、索引、执行计划和 专门攻击指标定义的测试数据，而不会停在语法正确。
作答前需要澄清的问题
- 哪一个事件作为用户锚点？ 本文使用报告区间内最早的访问，即使该用户在区间前也访问过。
“历史首次访问”需要完整历史数据和另一种队列筛选。
- 漏斗是否有序？ 是。注册必须在锚点访问后，购买必须在被选中的注册后。只检查无序事件是否
存在，计算的是另一项指标。
- 窗口从哪里开始、在哪里结束？ 从锚点访问开始，包含
visit_time + 24 hours这一时刻；
注册后不会重新开始计时。
- 后续步骤能否晚于
end_at？ 可以，只要仍在该用户的 24 小时窗口内。end_at只筛锚点访问，
不应截短转化机会。
- 同一时间戳怎么排序？ 先比较
event_time，再比较event_id，依赖题目明确的生产方保证。
没有可靠序列时，同一时刻的步骤顺序就是未知。
- 转化率分母是什么？ 两个比例都用
visited_users。如果购买率用注册用户数作分母，那是步骤间
转化率，列名和口径都应另行说明。
- 空输入返回什么？ 计数为 0；分母不存在，所以比例为 null。用
NULLIF防止除零。 - 报告何时最终稳定？ 完整性水位必须覆盖所有队列用户的 24 小时机会窗口，同时还要执行约定的
迟到数据处理策略。
30 秒回答框架
“我会先把队列整理成每个用户一行：筛选半开报告区间内的访问，按时间和事件 ID 排序，保留第一条。 对每个锚点，用一次左侧横向连接查找在访问之后且 24 小时内最早的注册；再用第二次横向连接，从被 选中的注册之后查找最早购买，同时仍使用原访问的 24 小时截止点。这样每个访问用户只产生一行进度。 最后用带过滤条件的计数统计到达注册和购买的用户，并都除以访问人数。我会测试事件倒序、重复事件、 相同时间戳、24 小时边界、重复访问、空队列和报告成熟度。”
分步深入解析
先构造队列。ROW_NUMBER() 之前就完成区间筛选，表达的正是“报告区间内最早的访问”。窗口排序加入 event_id，可以在多次访问时间相同时稳定选出锚点。
WITH ranked_visits AS (
SELECT
user_id,
event_id AS visit_event_id,
event_time AS visit_time,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY event_time, event_id
) AS visit_rank
FROM events
WHERE event_name = 'visit'
AND event_time >= :start_at
AND event_time < :end_at
),
cohort AS (
SELECT user_id, visit_event_id, visit_time
FROM ranked_visits
WHERE visit_rank = 1
)
SELECT *
FROM cohort;完整查询使用两个相互依赖的横向子查询。PostgreSQL 会用左侧行的值计算 LATERAL 子查询。 LEFT JOIN LATERAL 在没有合格事件时仍保留队列行，这正是漏斗需要的行为：从未注册的访问用户仍在 分母中。
WITH ranked_visits AS (
SELECT
user_id,
event_id AS visit_event_id,
event_time AS visit_time,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY event_time, event_id
) AS visit_rank
FROM events
WHERE event_name = 'visit'
AND event_time >= :start_at
AND event_time < :end_at
),
cohort AS (
SELECT user_id, visit_event_id, visit_time
FROM ranked_visits
WHERE visit_rank = 1
),
progress AS (
SELECT
c.user_id,
c.visit_time,
s.signup_time,
p.purchase_time
FROM cohort AS c
LEFT JOIN LATERAL (
SELECT
e.event_id AS signup_event_id,
e.event_time AS signup_time
FROM events AS e
WHERE e.user_id = c.user_id
AND e.event_name = 'signup'
AND (
e.event_time > c.visit_time
OR (
e.event_time = c.visit_time
AND e.event_id > c.visit_event_id
)
)
AND e.event_time <= c.visit_time + INTERVAL '24 hours'
ORDER BY e.event_time, e.event_id
LIMIT 1
) AS s ON TRUE
LEFT JOIN LATERAL (
SELECT e.event_time AS purchase_time
FROM events AS e
WHERE e.user_id = c.user_id
AND e.event_name = 'purchase'
AND s.signup_time IS NOT NULL
AND (
e.event_time > s.signup_time
OR (
e.event_time = s.signup_time
AND e.event_id > s.signup_event_id
)
)
AND e.event_time <= c.visit_time + INTERVAL '24 hours'
ORDER BY e.event_time, e.event_id
LIMIT 1
) AS p ON TRUE
)
SELECT
COUNT(*) AS visited_users,
COUNT(*) FILTER (WHERE signup_time IS NOT NULL) AS signed_up_users,
COUNT(*) FILTER (WHERE purchase_time IS NOT NULL) AS purchased_users,
ROUND(
COUNT(*) FILTER (WHERE signup_time IS NOT NULL)::numeric
/ NULLIF(COUNT(*), 0),
4
) AS signup_rate,
ROUND(
COUNT(*) FILTER (WHERE purchase_time IS NOT NULL)::numeric
/ NULLIF(COUNT(*), 0),
4
) AS purchase_rate
FROM progress;注册查找同时证明三件事：事件属于同一用户、元组顺序严格晚于锚点、时间位于锚点的 24 小时窗口内。 排序加 LIMIT 1 会选出唯一且稳定的事件。购买查找依赖已选中的注册，因此会忽略注册前的购买，同时 仍能找到后来的有效购买。它的截止时间继续引用 c.visit_time；若改为从注册起算，查询就会意外给出 最多 48 小时。
对固定三步有序漏斗，选择最早合格事件的贪心策略是正确的。选择最早注册不会排除“选更晚注册时才 能匹配”的购买：任何晚于更晚注册的购买，也一定晚于更早注册，而且共同截止点没有变化。选择最早 合格购买同理。每次查找后的不变量是：已选链条有效，并为剩余步骤留下尽可能长的窗口后缀。
progress 每个队列用户恰好一行，因为每个横向子查询至多返回一行。带过滤条件的聚合因此直接数的 就是用户，无需 DISTINCT。结果还应满足 purchased_users <= signed_up_users <= visited_users， 这是一组很实用的结果级断言。
应避开这类看似简洁的独立最小值：
MIN(CASE WHEN event_name = 'signup' THEN event_time END),
MIN(CASE WHEN event_name = 'purchase' THEN event_time END)对于 visit(09:00), purchase(09:05), signup(09:10), purchase(09:20)，独立最小值会选到 09:05 的购买，可能错误地判定用户未完成漏斗。依赖式查询会先选 09:10 的注册，再选 09:20 的购买。 只有每个最小值都受上一步实际选中事件约束时，条件最小值才可能正确，这正是本题的核心难点。
可以从两个索引开始评估：
CREATE INDEX events_anchor_scan_idx
ON events (event_name, event_time, user_id, event_id);
CREATE INDEX events_user_step_lookup_idx
ON events (user_id, event_name, event_time, event_id);第一个索引对应队列构造时的事件名和时间范围，第二个对应反复执行的用户级步骤查找。索引会增加存储 和写放大，实际收益取决于选择性、数据聚集、表大小和 PostgreSQL 最终计划，应使用代表性数据运行 EXPLAIN (ANALYZE, BUFFERS)。对高频大规模报表，可以考虑带稳定序列字段的事件模型或增量维护的 用户旅程表，但必须保留同一队列与窗口口径。
一组紧凑的对抗性数据应覆盖：
1. purchase 在 visit 前 -> 只算访问
2. visit、purchase、signup -> 算注册，不算购买
3. visit、purchase、signup、后续 purchase -> 完成三步
4. 重复 signup 和 purchase -> 每一步仍只计一个用户
5. purchase 恰好在 visit + 24 小时 -> 计入
6. purchase 晚于 visit + 24 小时一微秒 -> 不计入
7. 时间戳相同 -> 由 event_id 决定顺序
8. 区间内多次 visit -> 仍以最早一次为锚点
9. 没有 visit -> 计数为 0，比例为 null还要测试报告生命周期。若 end_at 是 7 月 1 日零点，6 月 30 日 23:59 的访问最晚可在 7 月 1 日 23:59 转化；7 月 1 日零点运行报表还不完整。成熟度应依赖摄取水位，不能只看当前时间；迟到事件到达 后，要重算受影响队列。
高质量示例回答
“写 SQL 前，我会先确定归因规则：每位用户只有一次漏斗机会，以报告区间内最早访问为锚点。报告区间 是半开的，后续步骤可以晚于区间结束，但注册和购买都必须落在锚点后 24 小时内。同一时间戳依赖题目 保证的事件 ID 顺序。
我先筛选区间内访问，按每位用户的 (event_time, event_id) 排序，只保留第一条，得到正确分母粒度。 对每个锚点，我用左侧横向子查询按同一元组排序，取最早有效注册；第二个左侧横向子查询引用这次注册， 取最早有效购买，同时让截止点继续绑定访问。两个查找都有 LIMIT 1，所以进度表仍是一行一个访问用户； 没有到达的步骤保留 null，不会把用户删掉。
最终聚合统计所有进度行，再分别统计注册和购买时间非空的行。两个比例都除以访问队列人数，并用 NULLIF 处理空输入。我会断言 购买人数 <= 注册人数 <= 访问人数，逐阶段抽查事件链，并重点测试 倒序事件、重复步骤、相同时间戳和 24 小时边界。
生产环境中，只有摄取水位覆盖完整机会窗口后，我才会把最新队列标为最终结果。我会比较支持锚点扫描 和用户级步骤查找的索引前后计划。如果时间戳和事件 ID 不能可信表达顺序，我会先修复事件契约，因为 查询无法恢复缺失的因果信息。”
常见错误
- 只检查事件是否存在。 三个事件标记可能把
purchase → visit → signup也算成转化；漏斗必须
构造明确的有序链。
- 分别取每一步的首次时间。 最早购买可能在被选中注册前，即使后面另有一次购买完成有效路径。
- 无意中让每次访问都成为锚点。 粒度会从一次用户机会变成一次访问机会，计数与归因都会变化。
- 只用
event_time排序。 同一时间戳会导致结果不稳定；需要可信的序列键，否则应承认顺序未知。 - 每一步都重启窗口。 注册给 24 小时、购买再给 24 小时，不符合以锚点为起点的 24 小时口径。
- 用
end_at过滤后续步骤。 会缩短靠近报告边界用户的机会，应读取到每个锚点自己的截止时间。 - 使用内连接横向查询。 没有注册的访问用户会消失，从而夸大转化率。
- 统计原始事件行。 重试和重复操作可能让步骤人数大于队列人数。
- 误用前一步作为分母。 这算的是步骤间转化率，不是题目要求的访问累计转化率。
- 发布未成熟队列。 完整窗口被可靠数据覆盖之前，缺少后续事件不能证明用户已经流失。
延伸问题与回答
如果任意一次访问都可以开启有效旅程呢？
粒度需要改变。为每次访问生成候选锚点，分别匹配有效链，再按“每位用户最早完成的旅程”等规则归因。 不能只替换队列 CTE：重叠窗口可能争用同一个后续事件，产品口径还需明确事件能否复用。
如果每一步必须属于同一会话或商品呢？
从锚点携带关联键，并在两个横向查找中都要求该键一致。粒度会变成用户—会话或用户—商品。使用前还 要定义空键如何处理，以及关联键是否不可变。
十步漏斗怎么实现？
手写十个横向连接很难审查。可以根据数据库能力选择递归 SQL、原生序列或漏斗函数，或用状态机式 预处理遍历有序事件。无论实现如何，都要保留同一组不变量：明确锚点、步骤单调有序、共享截止时间和 可审计的归因规则。
事件迟到或乱序怎么办？
区分事件时间与摄取时间，发布完整性水位，并重算窗口内收到迟到数据的队列。事件到达后仍可按事件时间 排序，但在约定的迟到容忍期结束前，报表只能是暂定结果。
能否完全用窗口函数解决？
可以，例如扫描每个用户的有序事件流并携带状态；但单独使用 LAG 不够，因为步骤之间可能夹杂无关 事件和重复事件。横向查询能直接表达“下一步依赖已选中的上一步”。只有另一种写法的状态转移和归因 同样清楚，且实测计划更好时，才值得替换。