播面

面试题：美妆品牌消费高集中度用户分析

1. 题目背景

在电商数据分析中，识别“品牌忠诚度”极高的用户对于精准营销至关重要。本题旨在考察候选人对 窗口函数（Window Functions）、条件过滤（WHERE） 以及 分组聚合（GROUP BY） 的综合运用能力。

2. 示例数据

假设有一张用户订单明细表 user_orders，记录了用户的购买历史：

表名：user_orders

注：假设当前统计基准日期为 2023-12-31，过去 1 年的区间为 2023-01-01 至 2023-12-31。

3. 期望输出结果

结果判定解析：

• 101: 美妆总消费 1000 元，其中 Estee Lauder 消费 900 元（占比 90% > 80%），符合条件（排除了电子类消费）。
• 102: 美妆总消费 1000 元，单一品牌最高消费 500 元（占比 50% < 80%），不符合条件。
• 103: 美妆总消费 500 元，Chanel 消费 500 元（占比 100% > 80%），符合条件。
• 104: 美妆总消费 200 元，Chanel 消费 200 元（占比 100% > 80%），符合条件（排除了服饰类消费）。
• 105: 消费时间在 1 年前，不计入统计。

SQL 解答方案

使用 窗口函数 是一步到位的标准写法：

WITH beauty_spend AS (
    SELECT 
        user_id,
        brand,
        SUM(amount) AS brand_amount,
        SUM(SUM(amount)) OVER(PARTITION BY user_id) AS total_beauty_amount
    FROM user_orders
    WHERE category = '美妆'
      AND order_date >= DATE_SUB('2023-12-31', INTERVAL 1 YEAR) -- 实际面试中可用 CURRENT_DATE 替代
    GROUP BY user_id, brand
)
SELECT DISTINCT user_id
FROM beauty_spend
WHERE (brand_amount / total_beauty_amount) > 0.8;

面试深度解析

为了在面试中获得高分，不仅需要写出 SQL，还要能够向面试官清晰地阐述以下设计细节和优化思路：

1. 核心解题步骤拆解

• 第一步：数据清洗与过滤（Where 子句）
  面试官非常看重“先过滤再计算”的意识。这里必须在最内层过滤出 category = '美妆' 以及 order_date 在过去一年内的记录。注意：千万不能把非美妆品类的消费算作分母。
• 第二步：计算“用户-品牌”的消费额 &“用户”美妆总消费额
  在 WITH 临时表（CTE）中，我们使用了 GROUP BY user_id, brand：
  • SUM(amount)：代表该用户在特定品牌上的美妆消费。
  • SUM(SUM(amount)) OVER(PARTITION BY user_id)：这是一个嵌套窗口函数。内层 SUM(amount) 算出了每个品牌的分组和，外层 SUM(...) OVER(...) 则无视品牌，直接将该用户所有品牌的美妆消费加总，算出该用户的美妆总消费。
• 第三步：比例筛选
  最后通过 brand_amount / total_beauty_amount > 0.8 过滤出符合条件的用户。

2. 关键考点与常见陷阱

• 陷阱一：混淆了“美妆总消费”和“全品类总消费”
  题目要求的是“在购买‘美妆’品类时……集中在同一个品牌”。这意味着分母只能是美妆品类的消费（如用户 104 虽然买了解码 Nike 鞋，但不应计入分母）。如果在过滤 category = '美妆' 之前就计算了总消费，该题直接判错。
• 陷阱二：忽略时间限制
  遗漏 order_date >= DATE_SUB(..., INTERVAL 1 YEAR) 会导致将历史过期数据统计进来（如用户 105）。
• 陷阱三：分母为零/Null 讨论（加分项）
  在实际业务中，若 amount 存在负数（退款）或零，可能会导致分母为 0。在面试中主动提及“我们假设消费金额为正数，若有负数，需通过 HAVING total_beauty_amount > 0 规避分母为零异常”，会给面试官留下非常专业的印象。

3. 替代方案（不用窗口函数的写法）

如果面试官要求不用窗口函数，可以使用常规的 JOIN 方式：

SELECT a.user_id
FROM (
    -- 每个用户每个美妆品牌的消费
    SELECT user_id, brand, SUM(amount) AS brand_amount
    FROM user_orders
    WHERE category = '美妆' AND order_date >= '2023-01-01'
    GROUP BY user_id, brand
) a
JOIN (
    -- 每个用户的美妆总消费
    SELECT user_id, SUM(amount) AS total_amount
    FROM user_orders
    WHERE category = '美妆' AND order_date >= '2023-01-01'
    GROUP BY user_id
) b ON a.user_id = b.user_id
WHERE (a.brand_amount / b.total_amount) > 0.8;

对比分析：窗口函数写法只需要扫描一次表并进行一次 Shuffle 即可完成计算，而 JOIN 写法需要对原表进行两次聚合后再关联，在海量数据下，窗口函数版本的执行效率更高，代码结构也更为优雅。