面试题：计算用户历史最大连续登录天数

1. 题目背景

在电商、社交或游戏等行业中，用户的“连续登录天数”是衡量用户粘性（Retention）和活跃度（Engagement）的重要指标。面试中，这道题用于考察候选人对 窗口函数（Window Functions） 的掌握程度，以及解决实际业务场景中复杂逻辑的能力。

2. 示例数据

输入数据：user_login_log（用户登录日志表）

注意：同一天内用户可能会多次登录（如 102 号用户在 2023-10-01 登录了两次），在计算连续天数时需要先进行去重处理。

期望输出结果

3. SQL 解答方案（基于标准 SQL / Hive / MySQL 8.0+）

WITH u_logins AS (
    -- 第一步：去重。去除同一个用户在同一天的重复登录记录
    SELECT DISTINCT user_id, login_date
    FROM user_login_log
),
ranked_logins AS (
    -- 第二步：通过窗口函数对每个用户的登录日期进行排序
    -- 并用登录日期减去排序生成的序号，得到一个基准日期（grp_date）
    SELECT 
        user_id,
        login_date,
        -- DATE_SUB 用于将日期减去指定的序号天数。
        -- 如果日期是连续的，那么减去序号后得到的基准日期必定是相同的。
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) DAY) AS grp_date
    FROM u_logins
),
consecutive_groups AS (
    -- 第三步：根据用户和基准日期分组，计算每个连续登录区间的实际天数
    SELECT 
        user_id,
        grp_date,
        COUNT(*) AS consecutive_days
    FROM ranked_logins
    GROUP BY user_id, grp_date
)
-- 第四步：求出每个用户所有连续区间中的最大天数
SELECT 
    user_id,
    MAX(consecutive_days) AS max_consecutive_days
FROM consecutive_groups
GROUP BY user_id;

4. 核心解题思路深度剖析

这道题的精髓在于“如何将连续的日期转化为可分组的特征值”。我们可以将其拆解为以下 4 个步骤进行理解：

第一步：数据去重（容易忽略的坑）

现实业务中，用户一天可能登录多次。如果不做去重，ROW_NUMBER() 算出来的序号就会偏大，导致后续的日期相减逻辑失效。因此，首先使用 SELECT DISTINCT user_id, login_date 进行去重。

第二步：经典“日期减行号”法（算法核心）

对于用户 101，我们看看去重排序后的日期与 ROW_NUMBER() 的关系：

• 发现规律： 当日期连续递增时，其对应的 ROW_NUMBER() 也是连续递增的，两者同步增长。因此，login_date - ROW_NUMBER() 会得到一个完全相同的固定日期（如上面的 2023-09-30）。
• 当日期出现中断时（比如从 03号 直接跳到 05号），两者的差值就会发生改变，从而自动划分出了一个新的“连续区间基准日”（如 2023-10-01）。

第三步：区间计数

通过对 user_id 和 grp_date 进行 GROUP BY，再使用 COUNT(*)，就能轻易算出每个连续区间的长度。

• user_id: 101, grp_date: 2023-09-30 -> 连续天数为 3
• user_id: 101, grp_date: 2023-10-01 -> 连续天数为 2

第四步：取最大值

最后对 user_id 进行聚合，用 MAX(consecutive_days) 提取出该用户在历史所有连续登录区间里的最大值。

5. 面试加分项与拓展提问应对

如果候选人能够主动提及以下几点，在面试中会大加分：

1. 不同数据库的日期函数差异：

   • MySQL: 使用 DATE_SUB(date, INTERVAL n DAY)。
   • Hive / Spark SQL: 使用 date_sub(date, n)。
   • PostgreSQL: 使用 date - n 或 date - interval '1 day' * n。
   • 加分点提示： “在实际书写时，需要根据具体的数仓引擎来调整日期相减的语法。”

2. 大数量级下的性能优化：

   • 如果用户量及登录日志极大（百亿级），直接 DISTINCT 和全局 ROW_NUMBER 容易产生数据倾斜或内存溢出（OOM）。
   • 优化思路： 可以先按 user_id 的哈希值将数据分桶（Distribute By），或者在数仓的前置层做好天级别的预汇总，减少进入窗口计算的数据量。