基于本文回答

播面

0
评论

统计用户进行“搜索关键词 -> 点击商品 -> 加入购物车 -> 提交订单”的完整漏斗转化率。要求上述各步骤必须在 2 小时内顺次完成。

经典大厂数据分析面试题：电商漏斗转化率分析

1. 题目要求

在电商分析中，用户路径转化率（漏斗分析）是最核心的指标之一。
请编写 SQL 统计用户进行 “搜索关键词 -> 点击商品 -> 加入购物车 -> 提交订单” 的完整漏斗转化率。

具体限制条件：

  1. 步骤必须严格按照上述顺序顺次发生（例如：未点击商品直接加购的不计入步骤3）。
  2. 从起点（搜索）开始，到最终步骤（提交订单）的所有事件，必须在 2 小时 的窗口期内完成。
  3. 产出各步骤的触达人数、环比转化率（当前步骤人数 / 上一步骤人数）以及总体转化率（当前步骤人数 / 搜索人数）。

2. 示例数据

用户行为表：user_behavior

user_id action_type action_time 备注（面试时无需提供，供读者理解数据流向）
101 search 2023-10-27 10:00:00 101号用户起点
101 click 2023-10-27 10:15:00 满足：在搜索后，2小时内
101 cart 2023-10-27 10:30:00 满足：在点击后，2小时内
101 order 2023-10-27 11:30:00 满足：在加购后，且距离起点未超2小时。（完整转化）
102 search 2023-10-27 10:00:00 102号用户起点
102 click 2023-10-27 10:05:00 满足：在搜索后，2小时内
102 cart 2023-10-27 13:00:00 不满足：距离起点（10:00）已超过 2 小时。
103 search 2023-10-27 11:00:00 103号用户起点
103 click 2023-10-27 11:20:00 满足：在搜索后，2小时内
103 order 2023-10-27 11:30:00 不满足：跳过了“加入购物车(cart)”步骤。
104 click 2023-10-27 09:00:00 乱序事件，不计入起点
104 search 2023-10-27 12:00:00 104号用户起点
104 click 2023-10-27 12:10:00 满足：在搜索后
104 cart 2023-10-27 12:40:00 满足：在点击后，2小时内。（停留在加购）
105 search 2023-10-27 14:00:00 105号用户起点。（停留在搜索）

3. SQL 解答方案（基于标准 SQL / MySQL 8.0）

sql 
WITH base_funnel AS (
    -- 1. 提取每个用户的搜索事件，作为漏斗的起点
    SELECT 
        user_id, 
        action_time AS search_time
    FROM user_behavior
    WHERE action_type = 'search'
),
click_funnel AS (
    -- 2. 关联点击事件：必须在搜索之后，且在搜索发生后的 2 小时内
    SELECT 
        b.user_id,
        b.search_time,
        MIN(c.action_time) AS click_time
    FROM base_funnel b
    LEFT JOIN user_behavior c ON b.user_id = c.user_id 
        AND c.action_type = 'click'
        AND c.action_time > b.search_time
        AND c.action_time <= b.search_time + INTERVAL 2 HOUR
    GROUP BY b.user_id, b.search_time
),
cart_funnel AS (
    -- 3. 关联加购事件：必须在点击之后，且在整个漏斗起点（搜索）的 2 小时内
    SELECT 
        c.user_id,
        c.search_time,
        c.click_time,
        MIN(ca.action_time) AS cart_time
    FROM click_funnel c
    LEFT JOIN user_behavior ca ON c.user_id = ca.user_id 
        AND ca.action_type = 'cart'
        AND ca.action_time > c.click_time
        AND ca.action_time <= c.search_time + INTERVAL 2 HOUR
    GROUP BY c.user_id, c.search_time, c.click_time
),
order_funnel AS (
    -- 4. 关联下单事件：必须在加购之后，且在整个漏斗起点（搜索）的 2 小时内
    SELECT 
        ca.user_id,
        ca.search_time,
        ca.click_time,
        ca.cart_time,
        MIN(o.action_time) AS order_time
    FROM cart_funnel ca
    LEFT JOIN user_behavior o ON ca.user_id = o.user_id 
        AND o.action_type = 'order'
        AND o.action_time > ca.cart_time
        AND o.action_time <= ca.search_time + INTERVAL 2 HOUR
    GROUP BY ca.user_id, ca.search_time, ca.click_time, ca.cart_time
),
user_max_step AS (
    -- 5. 汇总每个用户达到的最深步骤（去重降维）
    SELECT 
        user_id,
        MAX(CASE WHEN search_time IS NOT NULL THEN 1 ELSE 0 END) AS is_search,
        MAX(CASE WHEN click_time IS NOT NULL THEN 1 ELSE 0 END) AS is_click,
        MAX(CASE WHEN cart_time IS NOT NULL THEN 1 ELSE 0 END) AS is_cart,
        MAX(CASE WHEN order_time IS NOT NULL THEN 1 ELSE 0 END) AS is_order
    FROM order_funnel
    GROUP BY user_id
)
-- 6. 计算各步骤累计人数、环比转化率、总体转化率
SELECT
    SUM(is_search) AS search_users,
    SUM(is_click) AS click_users,
    SUM(is_cart) AS cart_users,
    SUM(is_order) AS order_users,
    
    -- 环比转化率 (串联比上一步)
    '100.00%' AS search_to_click_rate, -- 起点默认为 100%
    CONCAT(ROUND(SUM(is_click) * 100.0 / SUM(is_search), 2), '%') AS click_to_cart_rate,
    CONCAT(ROUND(SUM(is_cart) * 100.0 / NULLIF(SUM(is_click), 0), 2), '%') AS cart_to_order_rate,
    CONCAT(ROUND(SUM(is_order) * 100.0 / NULLIF(SUM(is_cart), 0), 2), '%') AS order_to_pay_rate,
    
    -- 整体转化率 (对比漏斗第一步)
    CONCAT(ROUND(SUM(is_order) * 100.0 / SUM(is_search), 2), '%') AS overall_conversion_rate
FROM user_max_step;

4. 期望输出结果

根据上述示例数据，最终查询得出的漏斗指标如下：

search_users click_users cart_users order_users search_to_click_rate click_to_cart_rate cart_to_order_rate order_to_pay_rate overall_conversion_rate
5 4 2 1 100.00% 80.00% 50.00% 50.00% 20.00%

5. 面试官视角：核心考察点与高分回答解析

在面试中，这道题目不仅考察 SQL 的编写功底，更考察你对数据分析业务场景和底层计算引擎优化的理解。以下是应对面试官追问的答题策略：

核心考点 1：为何使用 LEFT JOIN 而非 INNER JOIN？

  • 解析：漏斗分析必须保留没有走完后续步骤的用户，以便统计流失率。如果使用 INNER JOIN，在第一步流失的用户会在后续关联中被彻底过滤掉，导致最终只能统计出完成全链路的用户，无法计算中间步骤的留失。

核心考点 2：如何严格限定“时间窗口”和“顺次发生”？

  • 解析：
    • 顺次发生：在 JOIN 条件中，不仅要写 c.user_id = b.user_id，还要写 c.action_time > b.search_time。这保证了下一步事件的发生时间必须在上一事件之后。
    • 2小时内：限制条件 c.action_time <= b.search_time + INTERVAL 2 HOUR，必须始终锚定漏斗的第一步（即 search_time），而不是上一级时间，否则整条链路累计时间会滚雪球般超出 2 小时。

核心考点 3：防错设计——如何规避分母为0的报错？

  • 解析：在计算转化率时，如果某一步骤的实际触达人数为 0，直接相除会导致 Division by zero 报错。在 SQL 中使用 NULLIF(SUM(is_click), 0) 是一种极其严谨的工程习惯，面试官对此会非常加分。

核心考点 4：高级进阶（大数据量下的性能瓶颈与优化）

如果面试官问：“当 user_behavior 有数亿条记录时，多次自关联（Self-Join）会导致数据倾斜和内存溢出，如何优化？”

满分回答思路：

  1. 单次扫描 + 窗口函数（避免 Join）：
    可以使用 ROW_NUMBER() 或 LAG() 窗口函数，在一次 Table Scan 中对每个用户的所有行为按时间排序，利用行级差异判断是否在 2 小时内，从而避免昂贵的多表自关联。
  2. 引擎特性（如 ClickHouse windowFunnel）：
    在实际生产中（如 ClickHouse），一般不手写自关联，而是利用内置的高性能漏斗函数：
    sql 
    SELECT 
    level, 
    count() AS user_cnt
FROM (
    SELECT 
        user_id,
        windowFunnel(7200)( -- 2小时 = 7200秒
            action_time,
            action_type = 'search',
            action_type = 'click',
            action_type = 'cart',
            action_type = 'order'
        ) AS level
    FROM user_behavior
    GROUP BY user_id
)
GROUP BY level;
    这种回答能直接证明你具备实际海量数据生产环境的处理经验。
右滑查看面试常问