SQL面试题1：连续登陆问题

引言

场景介绍：

许多互联网平台为了提高用户的参与度和忠诚度，会推出各种连续登录奖励机制。例如，游戏平台会给连续登录的玩家发放游戏道具、金币等奖励；学习类 APP 会为连续登录学习的用户提供积分，积分可兑换课程或其他福利。通过这些激励措施，平台希望用户能够养成持续使用产品的习惯，从而提升产品的活跃度和留存率。同时，对于平台运营者来说，分析用户的连续登录数据可以了解用户的使用习惯和忠诚度，进而优化产品功能和运营策略。

题目描述：

假设我们有一个记录用户登录信息的表，表名为 login_table，其中包含两个字段：uid（用户 ID）和 dt（登录日期）。现在需要完成以下三个任务：

1. 查询连续登录超过三天的用户：找出在一段时间内，连续登录天数大于三天的用户列表。这有助于平台识别出那些高度活跃且对产品有较高忠诚度的用户，以便进一步进行精细化运营和奖励。
2. 查询每个用户连续登录的最大天数：对于每个用户，统计其在所有登录记录中连续登录的最长时间段，这能帮助我们了解不同用户的活跃程度差异，为个性化运营提供数据基础。
3. 查询一个用户连续登录的最大天数（可隔一天）：在计算用户连续登录天数时，允许中间间隔一天，只要整体登录天数最多，就是我们要找的结果。比如用户在 1、3、5、6 日登录，那么其连续登录的最大天数为 6 天。这种统计方式可以更灵活地评估用户的活跃程度，考虑到了用户可能因为某些特殊情况中断一天登录，但整体仍保持较高的使用频率。

数据准备与代码实现

数据准备

1	2025-01-01
1	2025-01-02
1	2025-01-03
2	2025-01-07
2	2025-01-08
3	2025-01-09
3	2025-01-10
3	2025-01-12
3	2025-01-13

1. 查询连续登录超过三天的用户

思路：

1. 用户登录记录编号：利用row_number()函数按uid分区并依dt升序排序生成序号rn，实现对各用户登录时间进行排序编号
2. 计算连续登录首日：利用date_add函数将dt减去rn，计算每行对应的连续登录起始日期first_day。
3. 选出连续登录超过三天大用户：利用group by按uid和first_day分组，结合having筛选出分组行数大于等于3的记录，实现找出连续登录超三天的用户uid。

with data as (
    select 1 as uid,'2025-01-01' as dt union all
    select 1 as uid,'2025-01-02' as dt union all
    select 1 as uid,'2025-01-03' as dt union all
    select 2 as uid,'2025-01-07' as dt union all
    select 2 as uid,'2025-01-08' as dt union all
    select 3 as uid,'2025-01-09' as dt union all
    select 3 as uid,'2025-01-10' as dt union all
    select 3 as uid,'2025-01-12' as dt union all
    select 3 as uid,'2025-01-13' as dt
),
data2 as (
    select uid,dt,row_number