Stripe 面试：找出过去一年支付成功率下降的商户 Top 10。

考察要点

这道题面试官想看你是否懂：

1. 复杂业务逻辑的 SQL 拆解能力：如何将“支付成功率下降”这个模糊的业务需求，转化为精确的、可执行的 SQL 步骤（定义时间窗口、计算比率、比较差异、排序）。
2. 时间序列分析：如何在 SQL 中处理时间维度，定义滚动的时间窗口（“过去一年”），并对不同时间段的数据进行聚合比较。
3. 高级 SQL 特性：熟练运用 CTE (Common Table Expressions) 组织复杂查询，使用条件聚合（CASE WHEN）提升效率，以及利用窗口函数（RANK/DENSE_RANK）进行排序。

核心概念

面试者需要清晰地阐述以下几个核心概念来构建解决方案。

假设我们有一张支付流水表 payments：

 1CREATE TABLE payments (
 2    payment_id BIGINT PRIMARY KEY AUTO_INCREMENT,
 3    merchant_id BIGINT NOT NULL,
 4    amount DECIMAL(10, 2) NOT NULL,
 5    status ENUM('succeeded', 'failed', 'pending') NOT NULL,
 6    created_at DATETIME NOT NULL,
 7    INDEX idx_created_at_merchant (created_at, merchant_id)
 8);

1. 时间窗口定义 (Time Window Definition) “过去一年”是一个相对概念，需要精确化。在工程实践中，通常指两个连续且等长的时间段的对比。一个常见的定义是：

   • 当前周期 (Current Period)：过去 6 个月（例如，NOW() 到 NOW() - INTERVAL 6 MONTH）。
   • 先前周期 (Prior Period)：当前周期之前的 6 个月（例如，NOW() - INTERVAL 6 MONTH 到 NOW() - INTERVAL 12 MONTH）。 这种定义可以有效平滑掉季节性或短期波动带来的噪音。

2. 条件聚合 (Conditional Aggregation) 这是本题最高效的核心技巧。相比于多次扫描表（一次为当前周期，一次为先前周期），我们可以在一次全表（或索引范围）扫描中，使用 CASE WHEN 语句在 SUM 或 COUNT 内部完成对不同周期的统计。

   示例：一次性计算两个周期的总交易数和成功数。

   sql

   ⧉⛶

    1SELECT
    2    merchant_id,
    3    -- 先前周期的统计
    4    COUNT(CASE WHEN created_at >= NOW() - INTERVAL 12 MONTH AND created_at < NOW() - INTERVAL 6 MONTH THEN payment_id END) AS total_tx_prior,
    5    COUNT(CASE WHEN status = 'succeeded' AND created_at >= NOW() - INTERVAL 12 MONTH AND created_at < NOW() - INTERVAL 6 MONTH THEN payment_id END) AS succeeded_tx_prior,
    6    -- 当前周期的统计
    7    COUNT(CASE WHEN created_at >= NOW() - INTERVAL 6 MONTH THEN payment_id END) AS total_tx_current,
    8    COUNT(CASE WHEN status = 'succeeded' AND created_at >= NOW() - INTERVAL 6 MONTH THEN payment_id END) AS succeeded_tx_current
    9FROM
   10    payments
   11WHERE
   12    created_at >= NOW() - INTERVAL 12 MONTH -- 关键：WHERE 子句限定最大时间范围，减少扫描数据量
   13GROUP BY
   14    merchant_id;

3. 成功率计算与空值处理 成功率公式为：成功交易数 / 总交易数。 必须处理分母为零的情况（即某个商户在某个周期内没有任何交易），否则会引发除零错误。标准做法是使用 NULLIF(denominator, 0)。

   • rate = succeeded_tx / NULLIF(total_tx, 0)

4. 公共表表达式 (CTE - Common Table Expression) 使用 WITH 关键字定义的临时结果集。CTE 能极大地提高复杂查询的可读性和可维护性，将整个分析流程拆解为多个逻辑步骤，如：

   1. WITH PeriodStats AS (...)：计算每个商户在两个周期的原始统计数据。
   2. WITH RatesComparison AS (...)：基于上一步计算成功率和成功率降幅。
   3. Final SELECT ...：对降幅进行排序并取出 Top 10。

5. 窗口函数 (Window Functions) 用于解决 Top-N 问题的标准方式。RANK() / DENSE_RANK() / ROW_NUMBER() 都能实现排序。

   • RANK(): 跳跃排名 (1, 2, 2, 4)。如果降幅相同，商户排名并列。
   • DENSE_RANK(): 连续排名 (1, 2, 2, 3)。
   • ROW_NUMBER(): 连续唯一排名 (1, 2, 3, 4)。 在本题中，RANK() 通常是最佳选择，因为它能反映出降幅相同的商户。

多种解法

解法 1：CTE + 条件聚合 + 窗口函数 (推荐, MySQL 8.0+ / PostgreSQL)

这种解法逻辑清晰、性能优越，是现代 SQL 的标准答案。

• 版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server / Oracle
• 思路：一次扫描 payments 表，用条件聚合计算出两个周期的指标，然后在 CTE 中计算比率和降幅，最后用窗口函数 RANK() 排序。

 1WITH PeriodStats AS (
 2    -- 步骤1: 使用条件聚合，一次性扫描并统计两个周期的交易数据
 3    SELECT
 4        merchant_id,
 5        -- 先前周期 (12个月前 -> 6个月前)
 6        COUNT(CASE WHEN created_at >= NOW() - INTERVAL 12 MONTH AND created_at < NOW() - INTERVAL 6 MONTH THEN payment_id END) AS total_tx_prior,
 7        COUNT(CASE WHEN status = 'succeeded' AND created_at >= NOW() - INTERVAL 12 MONTH AND created_at < NOW() - INTERVAL 6 MONTH THEN payment_id END) AS succeeded_tx_prior,
 8        -- 当前周期 (6个月前 -> 现在)
 9        COUNT(CASE WHEN created_at >= NOW() - INTERVAL 6 MONTH THEN payment_id END) AS total_tx_current,
10        COUNT(CASE WHEN status = 'succeeded' AND created_at >= NOW() - INTERVAL 6 MONTH THEN payment_id END) AS succeeded_tx_current
11    FROM
12        payments
13    WHERE
14        created_at >= NOW() - INTERVAL 12 MONTH -- 缩小扫描范围，必须要有
15    GROUP BY
16        merchant_id
17),
18RatesComparison AS (
19    -- 步骤2: 计算成功率和降幅，并过滤掉无意义的数据
20    SELECT
21        merchant_id,
22        -- 使用 NULLIF 防止除零错误
23        1.0 * succeeded_tx_prior / NULLIF(total_tx_prior, 0) AS success_rate_prior,
24        1.0 * succeeded_tx_current / NULLIF(total_tx_current, 0) AS success_rate_current,
25        (1.0 * succeeded_tx_prior / NULLIF(total_tx_prior, 0)) - (1.0 * succeeded_tx_current / NULLIF(total_tx_current, 0)) AS rate_drop
26    FROM
27        PeriodStats
28    -- 过滤条件：确保商户在两个周期内都有足够的交易量，避免小样本噪音
29    WHERE
30        total_tx_prior > 50 AND total_tx_current > 50
31),
32RankedMerchants AS (
33    -- 步骤3: 使用窗口函数对降幅进行排名
34    SELECT
35        merchant_id,
36        success_rate_prior,
37        success_rate_current,
38        rate_drop,
39        RANK() OVER (ORDER BY rate_drop DESC) as rnk
40    FROM
41        RatesComparison
42    WHERE
43        rate_drop > 0 -- 只关心成功率下降的商户
44)
45-- 步骤4: 选出Top 10
46SELECT
47    merchant_id,
48    success_rate_prior,
49    success_rate_current,
50    rate_drop
51FROM
52    RankedMerchants
53WHERE
54    rnk <= 10;

• 时间/空间复杂度：主要开销在 PeriodStats 的 GROUP BY。如果使用 Hash Aggregation，时间复杂度为 $O(N)$；如果使用 Sort Aggregation，则为 $O(N \log N)$，其中 N 是过去一年内的记录数。空间复杂度为 $O(M)$，M 是商户数量。
• 索引依赖：idx_created_at_merchant (created_at, merchant_id) 索引至关重要。WHERE created_at >= ... 可以利用此索引进行范围扫描（range scan），大大减少需要处理的数据量。

解法 2：子查询 + JOIN (兼容 MySQL 5.7)

在不支持 CTE 或窗口函数的旧版本数据库中，可以使用子查询（派生表）和 LIMIT 来实现。

• 版本：✅ MySQL 5.7+
• 思路：创建两个子查询，分别计算先前周期和当前周期的成功率，然后通过 INNER JOIN 将它们合并，计算差异后排序。

 1SELECT
 2    p.merchant_id,
 3    p.success_rate AS success_rate_prior,
 4    c.success_rate AS success_rate_current,
 5    (p.success_rate - c.success_rate) AS rate_drop
 6FROM
 7    (
 8        -- 子查询1: 计算先前周期的成功率
 9        SELECT
10            merchant_id,
11            SUM(CASE WHEN status = 'succeeded' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS success_rate
12        FROM
13            payments
14        WHERE
15            created_at >= NOW() - INTERVAL 12 MONTH AND created_at < NOW() - INTERVAL 6 MONTH
16        GROUP BY
17            merchant_id
18        HAVING
19            COUNT(*) > 50 -- 过滤交易量
20    ) AS p
21INNER JOIN
22    (
23        -- 子查询2: 计算当前周期的成功率
24        SELECT
25            merchant_id,
26            SUM(CASE WHEN status = 'succeeded' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS success_rate
27        FROM
28            payments
29        WHERE
30            created_at >= NOW() - INTERVAL 6 MONTH
31        GROUP BY
32            merchant_id
33        HAVING
34            COUNT(*) > 50 -- 过滤交易量
35    ) AS c ON p.merchant_id = c.merchant_id
36WHERE
37    p.success_rate > c.success_rate -- 确保是下降
38ORDER BY
39    rate_drop DESC
40LIMIT 10;

• 时间/空间复杂度：理论上，数据库可能会扫描两次 payments 表，性能可能劣于解法 1。但现代查询优化器可能会优化这个过程。复杂度与解法 1 类似，但常数因子可能更大。
• 索引依赖：同样需要 idx_created_at_merchant (created_at, merchant_id) 索引。每个子查询都能从索引中受益。

常见低分回答

1. 时间窗口定义错误：使用 YEAR(created_at) = YEAR(NOW()) - 1 等方式定义“去年”，这不符合“滚动一年”的业务口径，且无法处理跨年数据。
2. 忽略除零错误：直接使用 COUNT(succeeded) / COUNT(*)，当某个商户在某周期内无交易时，COUNT(*) 为 0，导致 SQL 执行失败。正确做法是 ... / NULLIF(COUNT(*), 0)。
3. 效率低下的多次扫描：不使用条件聚合，而是用 UNION ALL 配合 GROUP BY，或者像解法 2 那样用多个子查询。虽然解法 2 能跑，但面试官会期待你给出更优的条件聚合方案。
4. 忽略数据噪音：未过滤掉交易量过小的商户。一个商户从 1/1 (100%) 降到 0/1 (0%)，降幅 100%，但这个结果毫无意义。在 HAVING 或 WHERE 子句中加入 COUNT(*) > threshold 是体现业务思维和数据严谨性的关键。
5. 误用 JOIN：使用 LEFT JOIN 但未处理 NULL。如果一个商户在先前周期有交易，但在当前周期没有，LEFT JOIN 后当前周期的成功率为 NULL。计算 rate_drop 时 X - NULL 的结果是 NULL，该商户会被意外过滤掉。使用 INNER JOIN 在本题中是更直接的选择，因为它天然要求商户在两个周期都存在。

工程实践与优化

• 索引选择：对于 WHERE created_at >= ... GROUP BY merchant_id 这样的查询，复合索引 (created_at, merchant_id) 是最佳选择。created_at 在前，可以高效地进行范围过滤。
• 执行计划分析 (EXPLAIN)：
  • 高分表现：type 为 range（利用 created_at 索引），Extra 中没有 Using filesort（如果 GROUP BY 的列顺序与索引一致，可能可以避免排序）。Extra 出现 Using index condition 是好事，表示索引下推。
  • 低分表现：type 为 ALL（全表扫描），rows 巨大。这通常意味着 created_at 列上没有索引。
• 数据量级问题：当 payments 表达到数十亿甚至百亿级别时，即使有索引，实时查询也会非常慢，并给线上数据库带来巨大压力。
  • 解决方案：预聚合/物化视图。创建一个每日（或每小时）的商户统计表 merchant_daily_summary。
  sql

  ⧉⛶

   1CREATE TABLE merchant_daily_summary (
   2    summary_date DATE,
   3    merchant_id BIGINT,
   4    total_transactions INT,
   5    succeeded_transactions INT,
   6    PRIMARY KEY (summary_date, merchant_id)
   7);

  • 通过定时任务（如每晚）计算前一天的数据并写入此表。这样，原始查询就变成了对这个小得多的 summary 表的查询，可以在秒级完成，且不影响主业务库。
• 并发与锁：这是一个分析型查询（AP），应在只读副本（Read Replica）上执行，避免对主库（Primary）造成写入阻塞或锁竞争。查询本身是 SELECT，通常不会加写锁，但长时间运行的查询会消耗大量 CPU 和 I/O 资源。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，这条 SQL 性能怎样？怎么优化？

   • 回答要点 1 (瓶颈分析)：即使有 (created_at) 索引，当数据量达到百亿级别时，扫描过去一年的数据（即使是索引扫描）仍然会涉及海量的 I/O 操作和 CPU 计算（聚合）。查询会从秒级慢到分钟级甚至小时级，无法满足实时分析需求，并可能拖垮数据库。
   • 回答要点 2 (终极方案)：唯一的出路是预计算。如上文所述，建立一个 merchant_daily_summary 聚合表。查询时，直接从这个聚合表计算过去 180 天 vs 之前 180 天的数据。这样，扫描的数据行数从 (每日交易数 * 365) 急剧下降到 (商户数 * 365)，性能提升几个数量级。这是典型的数据仓库/数据湖的 ETL/ELT 思维。

2. 追问：如何判断成功率的下降是“显著的”，而不仅仅是随机波动？

   • 回答要点 1 (统计学方法)：这是一个数据科学问题。可以引入统计检验，如卡方检验 (Chi-squared test)。对于每个商户，我们有两组数据（先前周期和当前周期的成功/失败次数），可以构建一个 2x2 的列联表。

     	Succeeded	Failed
     Prior	succeeded_tx_prior	total_tx_prior - succeeded_tx_prior
     Current	succeeded_tx_current	total_tx_current - succeeded_tx_current

   • 回答要点 2 (实施)：通过卡方检验计算出一个 p-value。如果 p-value 小于一个阈值（如 0.05），我们就可以认为两个周期的成功率差异是统计显著的。在 SQL 查询之后，可以用 Python/R 等工具对 Top N 的结果进行这一步的检验，以增加结论的可靠性。

3. 追问：如果数据库换成 TiDB/OceanBase 这种分布式数据库，答案有什么不同？

   • 回答要点 1 (SQL 不变，架构不同)：SQL 语法层面，解法 1（CTE+窗口函数）完全适用，因为这些分布式数据库高度兼容 MySQL 协议和语法。不同之处在于底层的执行模型。
   • 回答要点 2 (分布式执行计划)：查询会被拆解成多个子任务下推到存储节点（如 TiDB 的 TiKV）并行执行。这个过程叫协处理器下推 (Coprocessor Pushdown)。WHERE 过滤、部分的聚合操作会在靠近数据的地方完成，大大减少了网络传输。
   • 回答要点 3 (数据分布与 GROUP BY)：GROUP BY merchant_id 操作可能需要数据重分布 (Shuffle)。执行引擎会根据 merchant_id 的哈希值将数据发送到不同的计算节点，以便每个节点处理一部分商户的聚合。索引 (created_at, merchant_id) 依然至关重要，它可以让每个存储节点高效地过滤出自己负责的数据。因此，虽然底层架构变了，但 SQL 优化的大原则（使用索引、减少扫描数据量）依然适用。