数据分析/数据开发面试题：经典三阶段漏斗转化率计算

1. 题目背景与要求

题目描述：
现有用户行为日志表 user_behavior，记录了用户的点击（click）、加入购物车（add_to_cart）和支付（pay）行为。请编写 SQL 计算“点击 -> 漏斗加入购物车 -> 支付”的转化率。

具体要求：

1. 顺序性：三种行为必须在同一个用户身上依次发生（即：点击时间 < 加购时间 < 支付时间）。
2. 时效性：全链路（从点击到支付）必须在 24 小时内完成。
3. 输出指标：
   • 步骤 1（点击）的去重用户数
   • 步骤 2（点击后加购）的去重用户数
   • 步骤 3（点击后加购并支付）的去重用户数
   • 点击到加购的转化率（Click-to-Cart Rate）
   • 加购到支付的转化率（Cart-to-Pay Rate）
   • 整体转化率（Overall Conversion Rate）

2. 示例数据

用户行为日志表 user_behavior：

测试用例行为解析（用于验证逻辑）：

• 用户 101：点击(10:00) -> 加购(12:00) -> 支付(14:00)。顺序正确，总耗时 4 小时（<24h），属于成功转化的完整样本。
• 用户 102：点击(10:00) -> 加购(次日11:00)。时间间隔为 25 小时（>24h），不计入加购和支付。
• 用户 103：加购(09:00) -> 点击(10:00) -> 支付(11:00)。虽然有三种行为且在24小时内，但加购发生在点击之前，不符合“依次”发生的顺序要求，仅计入点击。
• 用户 104：点击(15:00) -> 加购(16:00)。符合前两步，计入点击和加购，未支付。
• 用户 105：仅有点击行为，计入点击。

3. 预期输出结果

基于上述示例数据，各阶段去重人数应为：点击 = 5人，加购 = 2人（101, 104），支付 = 1人（101）。

4. SQL 解决方案 (基于 PostgreSQL 标准语法)

WITH click_events AS (
    SELECT user_id, action_time AS click_time
    FROM user_behavior
    WHERE action_type = 'click'
),
cart_events AS (
    SELECT user_id, action_time AS cart_time
    FROM user_behavior
    WHERE action_type = 'add_to_cart'
),
pay_events AS (
    SELECT user_id, action_time AS pay_time
    FROM user_behavior
    WHERE action_type = 'pay'
)
SELECT
    COUNT(DISTINCT t1.user_id) AS click_users,
    COUNT(DISTINCT t2.user_id) AS cart_users,
    COUNT(DISTINCT t3.user_id) AS pay_users,
    
    -- 漏斗转化率计算，使用 NULLIF 防止除以 0 报错，并将结果格式化为百分比
    CONCAT(ROUND(COUNT(DISTINCT t2.user_id) * 100.0 / NULLIF(COUNT(DISTINCT t1.user_id), 0), 2), '%') AS click_to_cart_rate,
    CONCAT(ROUND(COUNT(DISTINCT t3.user_id) * 100.0 / NULLIF(COUNT(DISTINCT t2.user_id), 0), 2), '%') AS cart_to_pay_rate,
    CONCAT(ROUND(COUNT(DISTINCT t3.user_id) * 100.0 / NULLIF(COUNT(DISTINCT t1.user_id), 0), 2), '%') AS overall_conversion_rate
FROM 
    click_events t1
LEFT JOIN 
    cart_events t2
    ON t1.user_id = t2.user_id 
    AND t2.cart_time > t1.click_time -- 确保“点击 -> 加购”的顺序性
    AND t2.cart_time <= t1.click_time + INTERVAL '24 hour' -- 限制加购在点击后24小时内
LEFT JOIN 
    pay_events t3
    ON t2.user_id = t3.user_id 
    AND t3.pay_time > t2.cart_time -- 确保“加购 -> 支付”的顺序性
    AND t3.pay_time <= t1.click_time + INTERVAL '24 hour'; -- 确保全链路（从起点的点击算起）在24小时内

5. SQL 深度解析与面试应对策略

在面试中，仅仅写出上述 SQL 往往是不够的。面试官会通过这道题考查考生的边界思考能力、多引擎适配能力以及大吞吐量下的性能优化意识。

解析重点 1：时效性与顺序性的逻辑闭环

• 顺序性表达：通过 t2.cart_time > t1.click_time 和 t3.pay_time > t2.cart_time 实现了严格的先后顺序约束。
• 全链路 24 小时表达：在最右侧的 LEFT JOIN 中，我们将 t3.pay_time <= t1.click_time + INTERVAL '24 hour' 作为终点约束。因为已经存在 $t_{click} < t_{cart} < t_{pay}$ 的链条，只要 $t_{pay} - t_{click} \le 24\text{h}$ 成立，中间的 $t_{cart}$ 自然也被约束在起点的 24 小时之内，从而简化了条件。

解析重点 2：为什么要用 LEFT JOIN 结合 COUNT(DISTINCT)？

• 防止漏斗断层：不能使用 INNER JOIN。如果使用内连接，没有完成后续步骤的用户会被过滤掉，导致无法统计第一步（仅点击）的基数。
• 去重防脏数据：一个用户可能会有多次点击、多次加购。通过 COUNT(DISTINCT t2.user_id)，只要该用户在限定时间内有一次成功的“点击 -> 加购”路径，即被视作成功转化的 1 个独立用户，避免了单个用户因重复操作导致转化率超过 100% 的异常情况。

解析重点 3：不同数据库引擎的兼容性（面试加分项）

不同的 SQL 引擎在时间相加减（INTERVAL）的语法上存在差异。向面试官主动说明这一点可以体现丰富的实战经验：

• MySQL 语法：
  t2.cart_time <= DATE_ADD(t1.click_time, INTERVAL 24 HOUR)
• Hive SQL / Spark SQL 语法（通常先转换为 Unix 时间戳进行秒数计算）：
  CAST(t2.cart_time AS BIGINT) <= CAST(t1.click_time AS BIGINT) + 86400
• Presto / Trino 语法：
  t2.cart_time <= t1.click_time + INTERVAL '24' HOUR

解析重点 4：大数据场景下的性能调优（高阶加分项）

如果面试官追问：“如果用户行为日志表每天有数十亿条数据，这个自连接（Self-Join）会非常慢，甚至引起内存溢出，如何优化？”

可以提出“单表扫描 + 窗口函数/条件聚合”的方案（免去复杂的 Multi-Join）：

1. 思路：首先利用窗口函数（如 LEAD 或 LAG）将每个用户的所有行为按时间升序排列。
2. 状态收集：通过时间差判断，对满足条件的数据行打上标签。
3. 优势：该方案只需要对大表进行一次全表扫描和按 user_id 的分布式分区排序，规避了两个大表进行 JOIN 时可能产生的 Shuffle 压力与数据倾斜。