播面

面试题：找出购买过至少 3 种不同商品品类，但从未购买过“数码配件”品类的活跃用户

1. 题目背景与要求

在电商平台的数据分析中，运营团队常常需要根据用户的消费行为进行精准画像。本题旨在考察候选人对多表关联、分组聚合（GROUP BY）、去重计数（DISTINCT）以及条件过滤（尤其是“从未购买过某品类”的排除逻辑）的掌握程度。

具体要求：

• 活跃用户：在用户信息表中标识为活跃（is_active = 1）的用户。
• 购买品类数：该用户购买过的不同商品品类数量大于或等于 3。
• 排除条件：该用户在历史购买记录中从未出现过“数码配件”这一品类。

2. 示例数据表

表 1：用户信息表 user_info

表 2：订单明细表 order_details

3. 期望输出结果

结果说明：

• 用户 101：活跃用户，购买了“美妆”、“食品”、“服饰” 3 种不同品类（订单 1 与订单 4 重复品类已去重），且未购买过“数码配件”，符合条件。
• 用户 102：活跃用户，虽然购买了 3 种品类，但包含了“数码配件”，不符合条件。
• 用户 103：活跃用户，仅购买了 2 种品类，数量不足，不符合条件。
• 用户 104：购买了 3 种品类且无“数码配件”，但其为非活跃用户（is_active = 0），不符合条件。
• 用户 105：活跃用户，购买了“美妆”、“食品”、“图书”、“运动”共 4 种品类，且无“数码配件”，符合条件。

4. SQL 解决方案

方法一：基于 HAVING 子句的条件过滤（推荐，性能较优）

这种方法通过一次分组聚合，利用 SUM(CASE WHEN...) 判定用户是否购买过特定品类，避免了多重子查询或表连接。

SELECT u.user_id
FROM user_info u
JOIN order_details o ON u.user_id = o.user_id
WHERE u.is_active = 1
GROUP BY u.user_id
HAVING COUNT(DISTINCT o.category) >= 3
   AND SUM(CASE WHEN o.category = '数码配件' THEN 1 ELSE 0 END) = 0;

方法二：使用子查询排除法（直观易懂）

利用 NOT IN 或 NOT EXISTS 提前将购买过“数码配件”的用户过滤掉，再对剩余数据进行分组统计。

SELECT u.user_id
FROM user_info u
JOIN order_details o ON u.user_id = o.user_id
WHERE u.is_active = 1
  AND u.user_id NOT IN (
      SELECT DISTINCT user_id 
      FROM order_details 
      WHERE category = '数码配件'
  )
GROUP BY u.user_id
HAVING COUNT(DISTINCT o.category) >= 3;

5. SQL 深度分析与面试应对指南

考点拆解

1. 多表关联（JOIN）：活跃状态在 user_info 表中，而购买行为在 order_details 表中，需要通过 user_id 进行关联。面试时应注意，若存在没有订单的活跃用户，使用 INNER JOIN 会自动过滤他们，这符合本题“购买过商品”的大前提。
2. 去重计数（DISTINCT）：题目要求购买过“至少 3 种不同商品品类”。如果用户多次购买同一品类（如示例中用户 101 购买了两次美妆），直接使用 COUNT(category) 会导致计数虚高。必须使用 COUNT(DISTINCT category) 来获取真实的品类数。
3. “从未购买过”的逻辑表达：这是本题的难点。
   • 常见误区：在 WHERE 子句中写 WHERE category != '数码配件'。这属于典型错误，因为该条件只会过滤掉单条“数码配件”的订单明细，而该用户购买其他品类的订单仍会被保留，无法达到“排除整个用户”的目的。
   • 正确方案：
     • 方案 A（条件转数值法）：在 HAVING 中使用 SUM(CASE WHEN category = '数码配件' THEN 1 ELSE 0 END) = 0。其逻辑是，如果该用户的所有订单中包含“数码配件”，则求和结果大于 0；若等于 0，则代表从未购买过。
     • 方案 B（子查询排除法）：先查出购买过“数码配件”的所有 user_id 集合，再在主查询中用 NOT IN 排除。

性能与优化考量

• 方案对比：方法一（推荐方案）通常优于方法二。方法一在一个查询流中完成了分组、去重计数和条件排除，减少了对 order_details 表的二次扫描。
• 索引建议：在实际生产环境中，若 order_details 表数据量极大，建议在 (user_id, category) 上建立联合索引，以加速分组和过滤操作。

面试沟通加分项

在回答此题时，向面试官主动说明“为什么不能在 WHERE 中直接限制 category != '数码配件'”，并解释 “COUNT(DISTINCT category) 与 COUNT(category) 的区别”。这能向面试官展现出您不仅能写出 SQL，还具备严谨的边界条件思考能力与扎实的数据分析基本功。