播面

面试题：退款用户15天内再消费且未退款比例统计

1. 题目描述

在电商平台中，用户的流失与挽留是核心运营指标之一。请编写一个 SQL 查出：在发生过退款完成行为的用户中，有多少比例的用户在退款完成后的 15 天内，再次在平台下单且这笔新订单未发生过退款。

注：比例保留两位小数，并以百分数或小数形式展现。

2. 示例数据

订单表：orders

记录用户的下单历史。

退款表：refunds

记录订单的退款状态及完成时间。

3. 期望输出

4. SQL 解答 (MySQL 8.0+)

WITH completed_refunds AS (
    -- 步骤1：筛选出所有已完成退款的用户及其对应的退款完成时间
    SELECT DISTINCT
        o.user_id,
        r.refund_complete_time
    FROM refunds r
    JOIN orders o ON r.order_id = o.order_id
    WHERE r.refund_status = 'Completed'
),
valid_repurchases AS (
    -- 步骤2：寻找在退款完成15天内再次下单，且新订单未发生退款的用户
    SELECT DISTINCT
        cr.user_id
    FROM completed_refunds cr
    JOIN orders o ON cr.user_id = o.user_id
    LEFT JOIN refunds r ON o.order_id = r.order_id
    WHERE o.order_time > cr.refund_complete_time 
      AND o.order_time <= DATE_ADD(cr.refund_complete_time, INTERVAL 15 DAY)
      AND r.order_id IS NULL -- 关键点：退款表中无此新订单记录，表示未发生退款
)
-- 步骤3：计算比例
SELECT 
    COUNT(DISTINCT cr.user_id) AS total_refund_users,
    COUNT(DISTINCT vr.user_id) AS repurchased_users,
    CONCAT(
        ROUND(COUNT(DISTINCT vr.user_id) * 100.0 / COUNT(DISTINCT cr.user_id), 2), 
        '%'
    ) AS repurchase_ratio_pct
FROM completed_refunds cr
LEFT JOIN valid_repurchases vr ON cr.user_id = vr.user_id;

5. 核心考点与 SQL 分析

在面试中，这道题属于典型的“留存与回购”类分析题，主要考察以下几个多表关联和时序分析的能力：

① 明确分母（退款完成的用户集合）

• 考点：不能简单地对 refunds 表直接去重 count，必须关联 orders 表拿到 user_id。
• 状态过滤：题干指出是“在退款完成后的...”，因此必须过滤 refund_status = 'Completed'。例如数据中的 104 号用户，退款处于 Processing 状态，不应计入分母。
• 在本例中，满足条件的分母用户为：101, 102, 103, 106（共 4 人）。

② 明确分子（退款后 15 天内再消费且未退款）

这是本题的难点，需要拆解为三个子条件：

1. 时序关系：新订单时间必须晚于退款完成时间（o.order_time > cr.refund_complete_time）。
2. 时间窗口：新订单在退款完成 15 天内（o.order_time <= DATE_ADD(..., INTERVAL 15 DAY)）。
3. 未退款限制：通过 LEFT JOIN refunds r ON o.order_id = r.order_id，并限定 r.order_id IS NULL。
   • 以 103 用户为例：退款后 3 天虽然再次下单 o006，但该订单随后又发起了退款（r004），因此 103 用户不能算作分子。
   • 以 102 用户为例：退款后再次下单 o004 间隔了 22 天，超出了 15 天窗口，因此不能算作分子。
   • 101 和 106 用户：均在 15 天内再次下单，且新订单未退款，属于合格的分子（共 2 人）。

③ 比例计算中的细节

• 去重（DISTINCT）：一个用户可能存在多次退款，或退款后多次下单。在计算分子和分母时，必须使用 COUNT(DISTINCT user_id) 防止数据膨胀。
• 防止除零或整除截断：在 SQL 中进行除法运算时，要注意乘以 100.0 转换为浮点数，否则在某些数据库中（如 PostgreSQL）整数相除会直接截断为 0。
• 最终左关联（LEFT JOIN）：计算最终比例时，用分母表 LEFT JOIN 分子表。这样即使分子为 0，也能正确输出 0.00%，避免了 INNER JOIN 导致分母为空的情况。