基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

统计发生过退款/退货行为的用户中，在退款完成后的 15 天内再次在平台下单且未发生退款的用户比例

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

1. 题目描述

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

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

2. 示例数据

订单表：orders

记录用户的下单历史。

order_id user_id order_time order_amount
o001 101 2023-10-01 10:00:00 100.00
o002 101 2023-10-08 12:00:00 150.00
o003 102 2023-10-01 14:00:00 200.00
o004 102 2023-10-25 15:00:00 120.00
o005 103 2023-10-02 09:00:00 300.00
o006 103 2023-10-07 11:00:00 50.00
o007 104 2023-10-05 10:00:00 80.00
o008 105 2023-10-10 10:00:00 500.00
o009 106 2023-10-05 10:00:00 90.00
o010 106 2023-10-12 11:00:00 110.00
退款表：refunds

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

refund_id order_id refund_status refund_complete_time
r001 o001 Completed 2023-10-03 10:00:00
r002 o003 Completed 2023-10-03 14:00:00
r003 o005 Completed 2023-10-04 09:00:00
r004 o006 Completed 2023-10-09 11:00:00
r005 o007 Processing NULL
r006 o009 Completed 2023-10-07 10:00:00

3. 期望输出

total_refund_users repurchased_users repurchase_ratio_pct
4 2 50.00%

4. SQL 解答 (MySQL 8.0+)

sql 
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 导致分母为空的情况。
00:00
00:00