SQL Interview Prep · 课时

构建多步骤转化漏斗

Q: 「构建多步骤转化漏斗」这节课中我会学到什么？

统计到达每个有序步骤的用户，并计算各步骤的转化率。 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

统计到达每个有序步骤的用户，并计算各步骤的转化率。

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

漏斗问题真正考查什么

当面试官说“请构建一个注册漏斗”时，他们是在考查您能否统计到达每个有序步骤的去重用户数量，并表示步骤之间的流失情况。

漏斗通常包含这样的阶段：visit -> signup -> activate -> purchase。通常需要交付每个步骤一行，并包含用户数量和转化率。

统计用户，而不是事件（同一用户触发两次事件，仍然只算一个用户）。
步骤是有序的；到达第 3 步意味着已经通过了第 1 步和第 2 步。

您将获得的事件表

几乎每道漏斗题都会给您一张采用长表形式的事件表。可以想象它具有这样的结构：

user_id 执行操作的用户
event_name 例如“访问”“注册”“购买”
event_time 时间戳

每次操作占一行。您的任务是将它转换为按步骤统计的结果。在编写 SQL 之前，务必先与面试官确认确切的事件名称。

CREATE TABLE events (
  user_id    INT,
  event_name VARCHAR(50),
  event_time TIMESTAMP
);

统计单个步骤的用户数

先从简单的开始：有多少去重用户到达了某个步骤？使用 COUNT(DISTINCT user_id)，并按事件名称进行筛选。

这是每个漏斗的基础。如果您能清晰地统计一个步骤，就能统计所有步骤。

SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';

使用条件聚合统计所有步骤

面试中清晰的答案是使用条件聚合，一次扫描统计所有步骤：在 COUNT(DISTINCT ...) 中嵌套一个 CASE。

对于每个步骤，统计事件与该步骤匹配的去重用户。一次扫描即可得到一行步骤总数。

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'visit'    THEN user_id END) AS step1_visit,
  COUNT(DISTINCT CASE WHEN event_name = 'signup'   THEN user_id END) AS step2_signup,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;

隐藏的错误：步骤并没有按顺序排列

您刚才看到的查询有一个面试官很喜欢设置的陷阱。它会统计所有触发过“购买”的人，即使他们在数据中从未访问或注册过。

真正的漏斗要求每个后续步骤都是前一个步骤的子集。独立统计事件可能导致第 3 步人数多于第 2 步，而这在逻辑上不可能发生在漏斗中。

解决方法是将同一用户的各个步骤关联起来，通常先将数据压缩为每个用户一行。

使用标记为每个用户保留一行

稳健的模式是：将事件日志压缩为每个用户一行，并为用户是否完成过每个步骤设置一个布尔标志（用 0/1 表示）。MAX(CASE ...) 可以将长日志转换为按用户汇总的宽表。

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_steps;

强制执行步骤顺序

现在执行漏斗规则：只有当用户完成了之前的每一个步骤时，才将其计入第 N 步。用户到达“购买”这一步，只有在他们也访问并注册过的情况下才有意义。

将前置条件用 AND 连接起来后对标志求和，这样每个步骤才真正是前一步的子集。

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(did_visit)                                       AS step1_visit,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END)                       AS step2_signup,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END)  AS step3_purchase
FROM user_steps;

将计数转换为整洁的长表结果

面试官通常更喜欢每个步骤一行，而不是一行宽表。可以使用一个简短的 UNION ALL，为宽表总数添加步骤编号并转换成长表。

这样一来，下一步计算转化率和绘制图表都会容易得多。

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;

步骤间转化率

有两种重要的转化率，面试官会问您指的是哪一种：

步骤转化率：当前步骤的用户数除以前一个步骤的用户数。
整体转化率：当前步骤的用户数除以漏斗顶端的用户数。

使用 LAG 获取前一个步骤的计数，以计算步骤间转化率。请转换为小数类型，避免发生整数除法。

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;

从漏斗顶端计算整体转化率

计算整体转化率时，将每个步骤的用户数除以第一步的用户数。对漏斗按顺序使用 FIRST_VALUE，即可为每一行固定这个顶端数值。

务必向面试官说明，您通过乘以 100.0 防止了整数除法。

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;

整合完整的漏斗

这就是面试官希望看到的端到端答案：先生成按用户汇总的标志，强制执行步骤顺序，转换为长表，然后计算两种转化率。请大声讲解整个过程，并说明每个 CTE 的用途。

这种结构易于扩展：增加一个标志和一行 UNION ALL，就可以增加一个步骤。

WITH user_steps AS (
  SELECT user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS s1,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS s2,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
  FROM events GROUP BY user_id
),
totals AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
  SELECT 2, 'signup',   SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
  SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;

快速检查

面试官发现您的漏斗中“购买”有 95 位用户，但“注册”只有 80 位。最可能的原因是什么？

回顾：多步骤漏斗

现在您已经可以按照面试所期望的方式构建漏斗：

按有序步骤统计去重用户，绝不直接统计原始事件。
使用 MAX(CASE ...) 标志，将事件日志压缩为每个用户一行。
强制执行顺序，使每个步骤都是前一步的子集。
计算步骤间（LAG）和整体（FIRST_VALUE）转化率，并防止整数除法。

下一步：确保这些步骤确实按正确顺序发生，并且发生在规定的时间窗口内。

常见问题解答

「构建多步骤转化漏斗」课时是免费的吗？

是的 — 「构建多步骤转化漏斗」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 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