如何用SQL查询用户在LeetCode平台上的最大连续登录天数？

Question

**如何用SQL查询用户在LeetCode平台上的最大连续登录天数？** 在LeetCode平台上，记录用户的登录日期后，如何计算每个用户的最大连续登录天数是一个常见需求。假设有一张表`logins`，包含字段`user_id`和`login_date`。解决此问题的核心是利用日期差与行号差的技巧。首先按`user_id`和`login_date`排序，通过窗口函数`ROW_NUMBER()`生成行号，然后计算`login_date`与行号的差值作为分组依据。同一连续登录序列的差值相同。最后，按`user_id`和分组依据统计最大连续天数。此方法高效且适用于大规模数据，但需注意日期格式和时区问题，确保数据一致性。

Answer 1

1. 问题背景与需求分析

在LeetCode平台上，记录用户的登录日期后，如何计算每个用户的最大连续登录天数是一个常见需求。假设我们有一张表`logins`，包含字段`user_id`和`login_date`，需要通过SQL查询实现这一目标。

具体需求如下：

• 按用户分组，统计每个用户的最大连续登录天数。
• 解决方法需要考虑大规模数据的性能问题。
• 确保日期格式一致性和时区统一性。

此问题的核心在于利用窗口函数和日期差值进行分组计算。

2. 解决思路与技术原理

解决此问题的关键是使用窗口函数`ROW_NUMBER()`生成行号，并结合日期差值进行分组。

1. 对`logins`表按`user_id`和`login_date`排序。
2. 使用`ROW_NUMBER()`为每条记录生成一个递增的行号。
3. 计算`login_date`减去行号作为分组依据，同一连续登录序列的差值相同。
4. 按`user_id`和分组依据统计连续天数，并取最大值。

以下是关键步骤的代码示例：

WITH RankedLogins AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM logins
),
GroupedLogins AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS group_key
    FROM RankedLogins
)
SELECT
    user_id,
    MAX(consecutive_days) AS max_consecutive_days
FROM (
    SELECT
        user_id,
        group_key,
        COUNT(*) AS consecutive_days
    FROM GroupedLogins
    GROUP BY user_id, group_key
) subquery
GROUP BY user_id;
    

3. 数据示例与结果验证

假设`logins`表的数据如下：

user_id	login_date
1	2023-10-01
1	2023-10-02
1	2023-10-04
1	2023-10-05
2	2023-10-01
2	2023-10-02
2	2023-10-03
2	2023-10-06
3	2023-10-01
3	2023-10-03

执行上述SQL查询后，结果应为：

user_id	max_consecutive_days
1	2
2	3
3	1

4. 流程图与逻辑梳理

以下是整个查询过程的流程图：

graph TD;
    A[原始数据] --> B{按user_id和login_date排序};
    B --> C[生成行号];
    C --> D{计算日期差值};
    D --> E[按差值分组];
    E --> F{统计连续天数};
    F --> G{取最大值};
    

此流程图清晰展示了从原始数据到最终结果的完整逻辑。

5. 注意事项与优化建议

在实际应用中需要注意以下几点：

• 确保`login_date`字段的日期格式一致，避免因格式问题导致错误。
• 如果存在时区差异，需提前将所有日期转换为统一时区。
• 对于大规模数据，可以考虑分区处理或使用分布式数据库提升性能。

此外，可以通过索引优化查询性能，例如为`user_id`和`login_date`创建复合索引。