播面

面试题目

题目背景：某电商平台希望分析 2025 年用户的跨品类消费行为转移情况。
具体要求：编写 SQL 查询，找出在 2025 年第一季度（Q1） 主要购买“母婴”品类（即该品类消费金额占该用户 Q1 总消费金额的 50% 以上），但在 2025 年第二季度（Q2） 主要购买“玩具”品类（即该品类消费金额占该用户 Q2 总消费金额的 50% 以上）的用户 user_id。

数据准备

用户订单表：user_orders

数据样例说明：

• 用户 101：
  • Q1 总消费 $100 + 30 = 130$，母婴消费 $100$（占比 $76.9\% > 50\%$），满足 Q1 条件；
  • Q2 总消费 $200 + 50 = 250$，玩具消费 $200$（占比 $80\% > 50\%$），满足 Q2 条件。
  • 应被检索出。
• 用户 102：
  • Q1 总消费 $200$，母婴占比 $100\% > 50\%$，满足 Q1；
  • Q2 总消费 $50 + 100 = 150$，玩具消费 $50$（占比 $33.3\% \le 50\%$），不满足 Q2。
  • 不应被检索出。
• 用户 103：
  • Q1 总消费 $50 + 100 = 150$，母婴消费 $50$（占比 $33.3\% \le 50\%$），不满足 Q1；
  • Q2 总消费 $150$，玩具占比 $100\% > 50\%$，满足 Q2。
  • 不应被检索出。

期望输出

SQL 解决方案

WITH q1_mother_baby AS (
    -- 步骤 1：筛选出 2025 年第一季度主要购买“母婴”的用户
    SELECT 
        user_id
    FROM user_orders
    WHERE purchase_date >= '2025-01-01' AND purchase_date <= '2025-03-31'
    GROUP BY user_id
    HAVING SUM(CASE WHEN category = '母婴' THEN amount ELSE 0 END) > SUM(amount) * 0.5
),
q2_toys AS (
    -- 步骤 2：筛选出 2025 年第二季度主要购买“玩具”的用户
    SELECT 
        user_id
    FROM user_orders
    WHERE purchase_date >= '2025-04-01' AND purchase_date <= '2025-06-30'
    GROUP BY user_id
    HAVING SUM(CASE WHEN category = '玩具' THEN amount ELSE 0 END) > SUM(amount) * 0.5
)
-- 步骤 3：取两个季度的交集用户
SELECT 
    q1.user_id
FROM q1_mother_baby q1
INNER JOIN q2_toys q2 ON q1.user_id = q2.user_id;

SQL 解析与面试技巧

1. 解题思路拆解（面试时如何向面试官表达）

• 分而治之（Divide and Conquer）：本题的关键是将复杂的跨季度条件拆解为两个独立的子问题。
  • 子问题 A：找出 2025 年 Q1 “母婴”消费占比 > 50% 的用户。
  • 子问题 B：找出 2025 年 Q2 “玩具”消费占比 > 50% 的用户。
• 条件求和（Conditional Aggregation）：在计算占比时，使用 SUM(CASE WHEN...) 来过滤特定品类的消费，再除以（或对比） SUM(amount) 总消费。这比多次关联同一张表更优雅、性能更好。
• 求交集：最后通过 INNER JOIN（或者 EXISTS、IN）将两个子查询的结果根据 user_id 关联，即可得到最终目标用户。

2. 关键语法解析

• HAVING 子句的妙用：
  • WHERE 用于在分组前过滤单行订单数据（限制时间范围在 Q1 或 Q2）。
  • HAVING 用于在 GROUP BY user_id 分组后，对聚合后的金额进行比例判断。
  • SUM(CASE WHEN category = '母婴' THEN amount ELSE 0 END) > SUM(amount) * 0.5 巧妙地规避了分母可能为 0 的风险，比直接用除法算比例更安全。

3. 面试加分项 / 性能优化讨论

如果面试官追问：“如果这张表有几亿条数据，该如何优化这个查询？”你可以从以下几个方面回答：

• 索引优化：确保在 purchase_date 和 user_id 上建立了联合索引，例如 (purchase_date, user_id, category, amount)。这样可以直接利用索引覆盖（Index Covering），无需回表查询物理行数据。
• 分区表：由于查询是严格基于时间季度的，如果 user_orders 表按天或按月进行了分区（Partitioning），数据库引擎可以利用分区裁剪（Partition Pruning）只扫描 2025 年上半年的分区，极大减少 I/O 消耗。
• 替代语法 EXISTS：在最后的关联部分，如果不需要展示 Q2 的其他数据，使用 EXISTS 替代 INNER JOIN 有时在特定数据库引擎中能获得更好的半连接（Semi-Join）优化。