面试题：计算用户点击促销 Banner 广告到最终购买该促销商品的平均耗时

1. 业务背景与口径定义

在电商转化分析中，衡量用户从“产生兴趣（点击广告）”到“决策购买（下单）”的流转效率是一个核心指标。
为了保证计算的准确性，我们需要定义以下业务口径：

1. 首次归因原则：计算用户首次点击某商品 Banner 广告，到其首次购买该商品之间的耗时。
2. 时间先后顺序：购买行为必须发生在点击行为之后（purchase_time > click_time）。
3. 指标单位：结果输出为平均耗时（小时数，保留 2 位小数）。

2. 示例数据

表 1：广告点击表 (ad_clicks)

表 2：订单表 (orders)

3. 面试考察点

• 逻辑严密性：能否排除“先买后点”（如用户 103 在 08:00 购买，09:00 才点击）的无效干扰数据。
• 多对多关联处理：同一用户可能多次点击、多次购买，如何准确锁定“点击后的首次购买”。
• 标准 SQL 函数应用：如何利用 WITH 表达式（CTE）让代码结构清晰，并使用时间差函数（如 TIMESTAMPDIFF）进行计算。

4. SQL 解决方案 (基于 MySQL 8.0+)

WITH FirstClicks AS (
    -- 步骤 1：获取每个用户对每个商品的最早点击时间（排除重复点击干扰）
    SELECT 
        user_id, 
        product_id, 
        MIN(click_time) AS first_click_time
    FROM ad_clicks
    GROUP BY user_id, product_id
),
PostClickPurchases AS (
    -- 步骤 2：关联订单表，筛选出发生在“最早点击”之后的购买记录，并取其最早的一次购买时间
    SELECT 
        fc.user_id,
        fc.product_id,
        fc.first_click_time,
        MIN(o.purchase_time) AS first_purchase_time
    FROM FirstClicks fc
    JOIN orders o 
      ON fc.user_id = o.user_id 
     AND fc.product_id = o.product_id
    WHERE o.purchase_time > fc.first_click_time  -- 核心条件：购买必须在点击之后
    GROUP BY fc.user_id, fc.product_id, fc.first_click_time
)
-- 步骤 3：计算所有转化用户的平均耗时（单位：小时）
SELECT 
    ROUND(AVG(TIMESTAMPDIFF(SECOND, first_click_time, first_purchase_time) / 3600.0), 2) AS avg_hours_to_buy
FROM PostClickPurchases;

5. SQL 代码深度解析与数据流转

为了向面试官展现扎实的功底，可以主动拆解测试数据的流转过程：

1. FirstClicks 临时表：
   • 过滤掉了用户 101 在 12:00:00 的二次点击，只保留 10:00:00 的首次点击。
2. PostClickPurchases 临时表（关键过滤步骤）：
   • 用户 101 (商品 501)：最早点击 10:00，后续购买 15:00，符合条件。耗时 $15 - 10 = 5$ 小时。
   • 用户 102 (商品 502)：最早点击 14:00，后续购买次日 14:00，符合条件。耗时 24 小时。
   • 用户 103 (商品 501)：虽然有 08:00 的购买记录，但因为早于点击时间 09:00 被 WHERE 子句过滤；系统准确锁定了点击后的首次购买 11:00。耗时 $11 - 9 = 2$ 小时。
   • 用户 104、用户 105：因无法匹配“既点击又购买”的链路，自动不参与计算。
3. 最终聚合：
   • 参与计算的耗时分别为：5小时、24小时、2小时。
   • 平均耗时：$(5 + 24 + 2) / 3 = 10.33$ 小时。

6. 面试官可能追问的扩展问题

追问 1：如果数据量极大（百亿级），上述 JOIN 性能很差，该如何优化？

• 解答思路：
  1. 限制时间窗口：通常点击到购买的转化不会跨越太久（如超过30天就失去广告归因意义）。可以在 JOIN 时加上分区键限制或时间范围限制，例如 WHERE o.purchase_time BETWEEN fc.first_click_time AND DATE_ADD(fc.first_click_time, INTERVAL 7 DAY)，利用分区裁剪和索引减少扫描数据量。
  2. 避免大表 JOIN，使用窗口函数：在离线计算（Hive/Spark SQL）中，可以将两张表通过 UNION ALL 合并成一张全量行为轨迹表，然后利用 ROW_NUMBER() 或 LEAD() / LAG() 窗口函数在单表内完成链路计算，避免高昂的 Shuffle Join 成本。

追问 2：如果我们要看不同“广告位置 (Banner位置)”的转化耗时，SQL 该怎么改？

• 解答思路：
  在 ad_clicks 表中必然有 banner_id 或 position 字段。我们只需在 FirstClicks 阶段将 banner_id 纳入 SELECT 和 GROUP BY 中，并在最终的输出中按 banner_id 进行 GROUP BY，即可对比不同位置广告的催化效率。