播面

面试题：计算 2025 年每个用户最长连续下单天数

1. 案例背景与数据准备

在电商数据分析中，用户的“连续活跃天数”或“连续购买天数”是评估用户粘性和流失风险的重要指标。以下是准备好的订单明细表 order_details 的样例数据（包含同一天多次下单、跨年数据、非连续数据等场景）：

订单明细表 (order_details)

2. 期望输出结果

• 101用户：2025-01-01 至 2025-01-03 连续 3 天，2025-01-05 只有 1 天。最长为 3。
• 102用户：去重后，2025-01-01 至 2025-01-02 连续 2 天；2025-01-04 至 2025-01-05 连续 2 天。最长为 2。
• 103用户：2024-12-31 被过滤。2025-01-01 至 2025-01-02 连续 2 天。最长为 2。

3. SQL 解答（基于 Hive SQL / Spark SQL 标准）

WITH filtered_orders AS (
    -- 步骤 1：过滤 2025 年数据并按用户和日期去重
    SELECT DISTINCT 
        user_id, 
        order_date
    FROM order_details
    WHERE order_date >= '2025-01-01' AND order_date <= '2025-12-31'
),
ranked_orders AS (
    -- 步骤 2：利用 row_number() 计算用户排序后的序号
    SELECT 
        user_id,
        order_date,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date) AS rn
    FROM filtered_orders
),
grouped_orders AS (
    -- 步骤 3：日期减去序号，生成连续日期分组基准 (date_diff)
    SELECT 
        user_id,
        order_date,
        DATE_SUB(CAST(order_date AS DATE), CAST(rn AS INT)) AS grp_date
    FROM ranked_orders
),
consecutive_counts AS (
    -- 步骤 4：统计每个用户每个连续区间的总天数
    SELECT 
        user_id,
        grp_date,
        COUNT(1) AS consecutive_days
    FROM grouped_orders
    GROUP BY user_id, grp_date
)
-- 步骤 5：求每个用户最大的连续天数
SELECT 
    user_id,
    MAX(consecutive_days) AS max_consecutive_days
FROM consecutive_counts
GROUP BY user_id;

4. 面试核心考点及 SQL 分析

此题是互联网公司（如阿里、美团、字节等）数据研发和数据分析岗位极高频的一道经典面试题。其核心考点和解题思路拆解如下：

考点一：数据清洗与去重（容易遗漏的关键点）

• 细节：同一天内用户可能产生多次购买。如果不去重，直接计算会导致同一个日期计算多次，从而破坏排序序号 ROW_NUMBER() 的连续性。
• 解决：在第一步（filtered_orders）中，使用 SELECT DISTINCT user_id, order_date 进行去重，同时使用 WHERE 子句锁死 2025 年 这一时间窗口。

考点二：“日期 - 序号 = 固定常数” 的经典双重排序算法（核心大招）

这是解决“连续性”问题的最优雅算法。

1. 原理解析：
   如果日期是连续的，那么“当前日期”与“递增序号”的变化幅度是一致的（每天增加 1，序号也增加 1）。
   • 例如对于用户 101：
     • 2025-01-01 -> 序号 1 -> 减去 1天 = 2024-12-31
     • 2025-01-02 -> 序号 2 -> 减去 2天 = 2024-12-31
     • 2025-01-03 -> 序号 3 -> 减去 3天 = 2024-12-31
       (这三天减出来的结果相同，说明它们属于同一个连续区间！)
     • 2025-01-05 -> 序号 4 -> 减去 4天 = 2025-01-01
       (结果发生改变，说明连续性中断，进入了新的连续区间。)
2. 函数选择：
   在 Hive / Spark SQL 中，常用 DATE_SUB(date, days)，而在 PostgreSQL 中，可以使用 order_date - INTERVAL '1 day' * rn。面试时应先明确数据库方言。

考点三：双层聚合

• 第一层聚合 (consecutive_counts)：通过 GROUP BY user_id, grp_date 计算每个用户每一段连续购买行为持续了多少天（COUNT(1)）。
• 第二层聚合 (最终输出)：通过 GROUP BY user_id 配合 MAX(consecutive_days) 筛选出该用户最长的一段连续天数。

面试通关避坑指南：

1. 主动向面试官确认细节：比如“如果用户在 2024-12-31 下单，2025-01-01 下单，2025-01-02 下单，那 2025 年的最长连续天数是算 2 还是 3？” 本题要求计算“在 2025 年期间”，因此答案应先过滤掉 2024 年数据，只算 2025 年内的 2 天。
2. 注意窗口函数的效率：大数据场景下，直接使用全局 ROW_NUMBER 会产生 Shuffle，在大表上性能敏感。先通过 DISTINCT 和 WHERE 缩小数据量是极佳的性能优化习惯，会给面试官留下非常专业的印象。