这是牛客SQL大厂真题精选第40题，属于‘窗口函数 + 日期处理’的综合考察题
通过本题你可以学到LEAD窗口函数的核心技巧

目录

一、题目简述

二、题目分析

1.题意解析

2.观察表结构及字段

2.1输入数据：

2.2输出数据：

三、解法思路

3.1方法

3.2重点步骤

四、完整MySQL实现代码

五、易错点

六、面试延伸（重点）

七、总结

一、题目简述

题目链接：查询连续登录的用户_牛客题霸_牛客网https://www.nowcoder.com/practice/9944210610ec417e94140ac09512a3f5?tpId=375&tqId=2371138&sourceUrl=%2Fexam%2Foj%3FquestionJobId%3D10%26subTabName%3Donline_coding_page

题目要求：

请查询连续登陆不少于3天的新注册用户？要求：输出user_id并升序排序。

注：登录表为单日随机一次登录数据，该题忽略单日多次登录情况。

考察重点：

  • 窗口函数LEAD的使用
  • 日期函数DATEDIFF的使用

二、题目分析

1.题意解析

原文描述：

某产品在2022年2月8日各端口用户注册信息及后几日登录信息如下：

用户注册信息表register_tb(user_id-用户id, reg_time-注册时间, reg_port-注册端口)

用户登录信息表login_tb(log_id-登录动作id,user_id-用户id, log_time-登录时间,  log_port-登录端口)

问题：请查询连续登陆不少于3天的新注册用户？要求：输出user_id并升序排序。

注：登录表为单日随机一次登录数据，该题忽略单日多次登录情况。

  •  条件1：连续登录>=3天的用户 -->  login_tb表 （log_time字段）
  •  条件2：输出user_id 并按user_id升序排序 --> login_tb表、register_tb表 （user_id字段）
  •  条件3：注:登录表为单日.....每个用户一天只有一条登录记录（无需去重）

“综上，本题要求找出 register_tb 表中注册且在 login_tb 表中出现连续三天登录记录的用户”

2.观察表结构及字段

2.1输入数据：

login_tb表（log_time字段）为日期类型，可使用日期类函数

  login_tb表（user_id字段）与 register_tb表（user_id字段）关联，可筛选新注册的用户

2.2输出数据：

        user_id:用户id  （此id既满足存在3天连续登录且在register_tb表）

user_id
1102
1106

三、解法思路

3.1方法

        在 login_tb 表使用LEAD()获取当前行后两行的 log_time 值（log_time2,log_time3），（详见3.2 Step1 表格数据示例）。再通过DATEDIFF()判断三天是否连续（均为1）。 最后关联 register_tb表筛选新注册用户。

3.2重点步骤

Step1：LEAD窗口函数取后两行数据

lead(log_time,1) over(partition by user_id order by log_time) log_time_2,
lead(log_time,2) over(partition by user_id order by log_time) log_time_3
LEAD函数处理后得到的数据示例（原表为login_tb）
user_idlog_timelog_time_2log_time_3
10032022-02-09 09:36:112022-02-10 10:43:012022-02-11 17:43:01
10032022-02-10 10:43:012022-02-11 17:43:01NULL
10032022-02-11 17:43:01NULLNULL
11012022-02-09 07:24:15NULLNULL
11022022-02-09 09:12:572022-02-10 09:37:012022-02-11 11:56:47
11022022-02-10 09:37:012022-02-11 11:56:47NULL
11022022-02-11 11:56:47NULLNULL
11042022-02-10 12:01:462022-02-11 14:52:37NULL
11042022-02-11 14:52:37NULLNULL
11062022-02-10 10:23:012022-02-11 16:56:272022-02-12 10:56:17
11062022-02-11 16:56:272022-02-12 10:56:17NULL
11062022-02-12 10:56:17NULLNULL

Step2：DATEDIFF函数进行时间相减

where datediff(log_time_2,log_time) = 1 and datediff(log_time_3,log_time_2) = 1

        如果结果 = 1，说明两天是连续的

Step3: 对Step1、Step2处理得到的user_id进行去重（可做可不做 不影响答案正确性）

        若同一用户存在多段连续登录，结果中会重复出现，可使用DISTINCT去重，但不影响最终输出结果。        

Step4：两表通过user_id关联

    register_tb r
join
    users u
on
    r.user_id = u.user_id

四、完整MySQL实现代码

with login_seq as(
    -- 使用 LEAD() 构造后两日登录时间
    select
        user_id,
        log_time,
        lead(log_time,1) over(partition by user_id order by log_time) log_time_2,
        lead(log_time,2) over(partition by user_id order by log_time) log_time_3
    from
        login_tb
),
valid_users as(
    -- 筛选连续三天登录的用户
    select
        -- distinct
        user_id
    from
        login_seq
    where datediff(log_time_2,log_time) = 1
      and datediff(log_time_3,log_time_2) = 1
)
# 关联注册表，输出最终结果
select r.user_id
from register_tb r
join valid_users v on r.user_id = v.user_id
order by r.user_id

五、易错点

本题易错点解析
错误写法问题说明正确写法
LAD()函数名写错，应为 LEAD()LEAD(log_time,1) OVER(...)
log_time2 - log_time直接相减返回NULL使用DATEDIFF比较日期差

六、面试延伸（重点）

延伸一：讲原理（展示思维）

        我使用LEAD()获取后一行的日期，再用DATEDIFF()判断是否相差 一天。

        连续登录其实是一个“时间序列分段问题”，可以用窗口函数或自连接实现

延伸二：讲可迁移性（展示经验）

        这种连续性判断思路，不止能用在登录分析，还能用在：

  1.连续下单天数

  2.连续访问天数

                3.连续订阅续费分析

延伸三：讲优化（展示深度）

“如果数据量大，可以先按 user_id + login_date 建索引，
         或者使用窗口函数预聚合后再过滤，减少自连接开销。”

七、总结

        本题是窗口函数处理时间序列问题的典型例题，掌握后可举一反三       

        以下是核心内容总结，可作复习使用。

知识点内容
核心函数LEAD()、DATEDIFF()
常见场景连续登录、连续购买、连续签到
延伸技巧序列分组、时间窗口聚合
面试话术“我先用窗口函数找相邻记录，再用时间差判断连续性”