播面

1. 面试题：二级裂变销售额统计

题目背景

在电商社交导购场景中，“裂变推广”是一种常见的获客和转化手段。
已知：二级裂变是指商品由 A 分享给 B，B 收到后又分享给 C，最终由 C 完成了购买。此时，C 的购买行为带来的销售额即为“二级裂变带来的销售额”。

题目要求

请编写 SQL 统计由于二级裂变带来的总销售额。
注：必须满足时间先后顺序，即：A 分享给 B 的时间 $\le$ B 分享给 C 的时间 $\le$ C 购买的时间，且分享和购买的必须是同一种商品。

2. 样例数据

表 1：商品分享记录表 share_records

记录用户之间分享商品的行为。

表 2：订单记录表 order_records

记录用户的商品购买及支付情况。

3. 期望输出结果

数据样例解析：

1. 链路 1 (P01)：101 -> 102 (10:00) $\rightarrow$ 102 -> 103 (12:00)。103 为二级裂变终点。103 在 14:00 购买了 P01，金额 150 元（有效，在分享后购买）。103 在 11:00 的购买（o2）无效，因为在分享前。贡献：150.00 元。
2. 链路 2 (P02)：104 -> 105 (09:00) $\rightarrow$ 105 -> 106 (08:00)。时间线错乱（B先分享给了C，才收到A的分享），不构成二级裂变。贡献：0 元。
3. 链路 3 (P04)：110 -> 111 (10:00) $\rightarrow$ 111 -> 112 (11:00)。112 在 12:00 购买了 P04，金额 300 元（有效）。贡献：300.00 元。
4. 总销售额 = $150.00 + 300.00 = 450.00$ 元。

4. SQL 解答

SELECT 
    SUM(o.pay_amount) AS total_fission_sales
FROM 
    share_records s1
-- 关联二级分享：s1 的接受者是 s2 的分享者
INNER JOIN share_records s2 
    ON s1.receiver_id = s2.sharer_id
   AND s1.product_id = s2.product_id
   AND s1.share_time <= s2.share_time
-- 关联最终购买订单
INNER JOIN order_records o 
    ON s2.receiver_id = o.buyer_id
   AND s2.product_id = o.product_id
   AND s2.share_time <= o.order_time;

5. SQL 分析与面试深度解析

Step 1: 核心思路——“自关联”构建裂变链

本题的难点在于如何用 SQL 表达 A $\rightarrow$ B $\rightarrow$ C 的层级关系。

• 我们将 share_records 表进行自关联（Self-Join）：
  • s1 代表 A 分享给 B 的记录。
  • s2 代表 B 分享给 C 的记录。
• 关联条件：
  1. s1.receiver_id = s2.sharer_id：前者的接受者是后者的分享者。
  2. s1.product_id = s2.product_id：必须是同一件商品（防止跨商品的混乱传播）。
  3. s1.share_time <= s2.share_time：时间约束，B 必须在收到分享后，才能分享给 C。

Step 2: 关联消费，完成闭环

有了完整的二级裂变链后，我们需要找到最终的买单人 C（即 s2.receiver_id）：

• 将裂变链与订单表 order_records o 进行 INNER JOIN：
  • s2.receiver_id = o.buyer_id：购买者是裂变链的最后一环 C。
  • s2.product_id = o.product_id：购买的是该裂变商品。
  • s2.share_time <= o.order_time：时间约束，C 必须在收到 B 的分享之后才进行购买。

Step 3: 面试官可能会追问的边缘场景（加分项）

如果在面试中，你能主动向面试官提出以下边缘场景并给出解决方案，会极大增加通过率：

1. 归属权重复计算问题（Attribution Overlap）：

   • 问题：如果 A 分享给 B，X 也分享给 B，B 再分享给 C。此时会产生两条裂变链：A -> B -> C 和 X -> B -> C。直接 SUM 会导致订单被计算两次。
   • 解决方案：通常业务上采用“首次归属”或“末次归属”原则。
     若采用“末次归属”（即离 B 转发时间最近的那次被分享），可以使用窗口函数 ROW_NUMBER() OVER(PARTITION BY s2.share_id ORDER BY s1.share_time DESC) 筛选出唯一的一条裂变链，再与订单表关联。

2. 性能优化（Performance）：

   • 在大数据量下，自关联 share_records 会非常消耗资源。
   • 优化手段：
     • 限制时间窗口：例如，裂变通常发生在极短时间内。可以在关联条件中加入：s2.share_time <= s1.share_time + INTERVAL 7 DAY（限制在一周内完成裂变）。
     • 对 product_id 和 share_time 建立联合索引，加快 JOIN 的检索速度。