面试题：计算 7 天滚动去重买家数 (7-day Rolling Active Buyers)

1. 题目背景与要求

在电商和用户行为分析中，“日活跃用户数 (DAU)” 是一个关键指标。但由于周内效应（如周末与工作日的用户行为差异），业务上常常需要观察 “过去 7 天内有购买行为的去重买家总数”（即 7-day Rolling Active Buyers），以此来平滑日间波动，更准确地评估用户留存和活跃趋势。

请编写 SQL，根据给定的每日购买流水表，计算每天对应的“过去 7 天内（包含当天）有购买行为的去重买家总数”。

2. 示例数据

输入表：purchases（购买流水表）

期望输出结果

数据解释（以 2023-10-08 为例）：

• 2023-10-08 的过去 7 天区间为：[2023-10-02, 2023-10-08]。
• 在此区间内有购买记录的买家及日期：
  • 10-02: 101, 103
  • 10-03: 104
  • 10-04: 101
  • 10-07: 105
  • 10-08: 101
• 去重后的买家为：101, 103, 104, 105，共 4 人。
• （注意：10-01 的买家 102 已超出 7 天范围，故不计入）。

3. SQL 解答

由于主流数据库（如 Hive、MySQL、Spark SQL）通常不支持在窗口函数中直接使用 COUNT(DISTINCT ...) 配合 RANGE 或 ROWS 滑动窗口（例如 COUNT(DISTINCT buyer_id) OVER (ORDER BY purchase_date RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) 会报错），因此最通用且标准的解法是采用 自关联 (Self-Join)。

标准 SQL 实现（自关联法）

SELECT 
    t1.purchase_date,
    COUNT(DISTINCT t2.buyer_id) AS rolling_7d_active_buyers
FROM 
    (SELECT DISTINCT purchase_date FROM purchases) t1
JOIN 
    purchases t2 
ON 
    t2.purchase_date BETWEEN DATE_SUB(t1.purchase_date, INTERVAL 6 DAY) AND t1.purchase_date
GROUP BY 
    t1.purchase_date
ORDER BY 
    t1.purchase_date;

4. 核心考点与 SQL 分析

这道题是数据分析师和数据开发面试中的“常客”，主要考察面试者对 滑动窗口模拟、去重统计机制、自关联以及性能优化 的理解。

核心解题步骤剖析：

1. 确定日期骨架 (t1)：

   • 如果我们直接拿 purchases 和自身关联，因为一天中可能有同一买家的多条购买记录，会导致数据急剧膨胀。
   • 因此，我们先通过 (SELECT DISTINCT purchase_date FROM purchases) t1 提取出所有有交易的日期作为主表（骨架）。

2. 不等值关联条件 (ON BETWEEN ... AND ...)：

   • 将主表 t1 的每一天，去关联流水表 t2 中 “当前日期及前 6 天” 范围内的所有明细。
   • 条件：t2.purchase_date BETWEEN t1.purchase_date - 6天 AND t1.purchase_date。
   • 这样，对于 t1 的任意一天，关联过来的 t2 数据就是该天过去 7 天内的全部购买流水。

3. 分组与去重计数 (COUNT(DISTINCT))：

   • 按 t1.purchase_date 分组。
   • 对关联过来的 t2.buyer_id 进行去重计数 COUNT(DISTINCT t2.buyer_id)，即可得到该天的 7 天滚动活跃买家数。

5. 面试加分项与进阶思考

在面试中，如果能主动提及以下几点，会极大增加通过率：

优化 1：数据预去重（提升性能）

在大数据场景下，直接对 7 天的原始流水进行 COUNT(DISTINCT) 会非常慢（因为有大量的重复购买数据）。
优化思路：先按“天+买家”进行轻度汇总去重，然后再进行自关联。

WITH daily_unique_buyers AS (
    -- 先找出每天有哪些去重的买家，极大减少参与 Join 的数据量
    SELECT purchase_date, buyer_id
    FROM purchases
    GROUP BY purchase_date, buyer_id
)
SELECT 
    t1.purchase_date,
    COUNT(DISTINCT t2.buyer_id) AS rolling_7d_active_buyers
FROM 
    (SELECT DISTINCT purchase_date FROM daily_unique_buyers) t1
JOIN 
    daily_unique_buyers t2 
ON 
    t2.purchase_date BETWEEN DATE_SUB(t1.purchase_date, INTERVAL 6 DAY) AND t1.purchase_date
GROUP BY 
    t1.purchase_date
ORDER BY 
    t1.purchase_date;

优化 2：如果某天没有购买数据怎么办？（日历补全）

题目中给定的输入是“有购买的流水”。如果某天刚好没有任何人购买，在 purchases 表中就没有这一天的记录。但在实际业务中，即使今天没人买，我们也需要输出今天（此时 7 天内活跃数可能因为前几天有数据而不为 0）。

• 面试官追问：如何解决日期断档问题？
• 回答：应该先生成一个连续的日期维表 (Calendar Table) 作为 t1，然后用 LEFT JOIN 关联 purchases t2，以确保输出的日期是完整且连续的。