数据库 SQL 面试题：流失用户分析

1. 题目背景

在电商、SaaS 或内容消费平台中，识别“流失用户”（Churned Users）是运营部门的核心诉求。本题旨在考察候选人利用 SQL 进行时间窗口指标计算、条件聚合（Conditional Aggregation）以及子查询/排除查询的掌握程度。

2. 示例数据

假设有一张用户消费订单表 user_orders，记录了用户在 2025 年的每一笔消费：

订单表 (user_orders)

样例数据分析：

• 用户 101：上半年消费 3000 + 2500 = 5500 元（> 5000），下半年无消费。（符合条件）
• 用户 102：上半年消费 6000 元（> 5000），但下半年消费了 500 元。（排除）
• 用户 103：上半年消费 4000 元（<= 5000）。（排除）
• 用户 104：上半年消费 1000 + 4500 = 5500 元（> 5000），下半年无消费。（符合条件）
• 用户 105：上半年无消费，下半年消费 8000 元。（排除）

期望输出结果：

3. 面试要求

请编写一条 SQL 查询语句，找出在 2025 年上半年（1-6月）累计消费金额超过 5000 元，但在下半年（7-12月）消费金额为 0（即没有任何消费记录）的用户，输出其 user_id 以及上下半年的消费金额。

4. 参考答案

在面试中，通常有以下两种主流的解题思路。

方法一：条件聚合（Conditional Aggregation）—— 推荐解法

这是最优雅且性能较好的写法，只需要对表进行一次全表扫描（Single Pass）。

SELECT 
    user_id,
    SUM(CASE WHEN order_date BETWEEN '2025-01-01' AND '2025-06-30' THEN amount ELSE 0 END) AS h1_spent,
    SUM(CASE WHEN order_date BETWEEN '2025-07-01' AND '2025-12-31' THEN amount ELSE 0 END) AS h2_spent
FROM 
    user_orders
WHERE 
    order_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY 
    user_id
HAVING 
    SUM(CASE WHEN order_date BETWEEN '2025-01-01' AND '2025-06-30' THEN amount ELSE 0 END) > 5000
    AND SUM(CASE WHEN order_date BETWEEN '2025-07-01' AND '2025-12-31' THEN amount ELSE 0 END) = 0;

方法二：子查询排除法（Except / Not In / Left Join）

这种方法更符合直觉，先找出上半年消费超 5000 的人，再排除下半年有消费的人。

WITH h1_users AS (
    -- 步骤1：找出上半年消费 > 5000 的用户
    SELECT 
        user_id,
        SUM(amount) AS h1_spent
    FROM 
        user_orders
    WHERE 
        order_date BETWEEN '2025-01-01' AND '2025-06-30'
    GROUP BY 
        user_id
    HAVING 
        SUM(amount) > 5000
),
h2_users AS (
    -- 步骤2：找出下半年有消费的用户
    SELECT DISTINCT 
        user_id
    FROM 
        user_orders
    WHERE 
        order_date BETWEEN '2025-07-01' AND '2025-12-31'
)
-- 步骤3：用左连接排除下半年有消费的用户
SELECT 
    h1.user_id,
    h1.h1_spent,
    0.00 AS h2_spent
FROM 
    h1_users h1
LEFT JOIN 
    h2_users h2 ON h1.user_id = h2.user_id
WHERE 
    h2.user_id IS NULL;

5. 核心考点解析（如何应对面试官）

面试官在考察这道题时，主要看重以下几个维度：

1. 时间维度的过滤与处理

• 考点：如何准确界定“上半年（1-6月）”和“下半年（7-12月）”。
• 细节：直接使用 order_date BETWEEN '2025-01-01' AND '2025-06-30'。在实际业务中，如果 order_date 是 DATETIME 类型（包含时分秒），使用 BETWEEN 时要注意边界问题（如 2025-06-30 23:59:59）。在面试中，可以主动提及：“假设 order_date 是日期类型，如果是精确到秒的，建议使用 >= '2025-01-01' AND < '2025-07-01'”，这会是很好的加分项。

2. 条件聚合（Conditional Aggregation）

• 考点：SUM(CASE WHEN ... THEN ... ELSE 0 END) 的运用。
• 分析：这是解决“多时间段对比”问题的杀手锏。它允许我们在同一个 GROUP BY user_id 流程中，同时计算出两个不同时间段的指标。相比于多次 JOIN 或多个子查询，这种方式对数据库的 I/O 消耗最小，因为它只需要扫描一次数据集。

3. “消费金额为 0”的陷阱处理

• 考点：如何表达“下半年消费为 0”。
• 分析：在真实数据库中，下半年没消费的用户在下半年通常是没有记录的。
  • 如果使用方法一（条件聚合），没有记录的用户其 CASE WHEN 永远走 ELSE 0，最后 SUM 出来就是 0，完美符合 HAVING ... = 0。
  • 如果使用方法二（排除法），必须使用 LEFT JOIN ... WHERE h2.user_id IS NULL 或 NOT EXISTS。千万不要直接写 WHERE h2_amount = 0，因为没有记录代表的是 NULL，直接判断 amount = 0 会漏掉这些真正流失的用户。

4. 性能与优化（主动向面试官展示）

• 索引建议：如果这张表数据量极大，建议在 (order_date, user_id, amount) 上建立联合索引，以实现 Index Only Scan（覆盖索引），从而大幅提升查询性能。