SQL面试题每日一题之求解用户最长连续登录天数
之后会不定期更新每日一题sql系列。
SQL面试题每日一题系列内容均来自于网络以及实际使用情况收集，如与各大厂面试题有雷同，纯属巧合。
1.题目
问题：以下为多个用户每日登录记录数据，求解每个用户最长连续登录天数。
该问题为此还会衍生出，标记处连续登录n天用户。用户连续登录中出现的所有未登录日期等。具体之后进行分析计算演示，欢迎持续关注。
2.基础数据准备
create table if not exists temp.user_login_log (
`id` bigint comment '用户id',
`login_date` string comment '登录日期'
) comment '用户每日登录流水'
数据预览
| id | login_date |
|---|---|
| 1 | 2024-04-25 |
| 1 | 2024-04-26 |
| 1 | 2024-04-27 |
| 1 | 2024-04-28 |
| 1 | 2024-04-30 |
| 1 | 2024-05-01 |
| 1 | 2024-05-02 |
| 1 | 2024-05-04 |
| 1 | 2024-05-05 |
| 1 | 2024-05-06 |
| 1 | 2024-05-07 |
| 2 | 2024-04-25 |
| 2 | 2024-04-28 |
| 2 | 2024-05-01 |
| 2 | 2024-05-03 |
| 2 | 2024-05-04 |
3.问题分析
求解每个用户最长连续登录天数，考察的是对日期加减函数的使用以及对排序开窗函数的应用。排序开窗函数详见SQL窗口分析函数使用详解系列二之分组排序窗口函数
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
4.解题SQL
1.对原始数据排序生成序号
这步很重要，作为判断是否连续的基础数据处理。
select id,login_date
,row_number() over(partition by id order by login_date) as rank_id
from temp.user_login_log
order by id,login_date
数据结果
| id | login_date | rank_id |
|---|---|---|
| 1 | 2024-04-25 | 1 |
| 1 | 2024-04-26 | 2 |
| 1 | 2024-04-27 | 3 |
| 1 | 2024-04-28 | 4 |
| 1 | 2024-04-30 | 5 |
| 1 | 2024-05-01 | 6 |
| 1 | 2024-05-02 | 7 |
| 1 | 2024-05-04 | 8 |
| 1 | 2024-05-05 | 9 |
| 1 | 2024-05-06 | 10 |
| 1 | 2024-05-07 | 11 |
| 2 | 2024-04-25 | 1 |
| 2 | 2024-04-28 | 2 |
| 2 | 2024-05-01 | 3 |
| 2 | 2024-05-03 | 4 |
| 2 | 2024-05-04 | 5 |
可以看到这里是按用户id分组，按日期进行了一个分别排序。
2.对排序进行做差值
因为如果是连续登录，那登录日期减去自增的排序就会始终是一个日期值。比如3号登录排序1,4号登录排序2,5号登录排序3。则他们之间的差值都是2。这就是连续。反之，如果差值不一致，就是不连续。
select id,login_date,date_sub(login_date,rank_id) as date_gap
from (
select id,login_date
,row_number() over(partition by id order by login_date) as rank_id
from temp.user_login_log
) a
order by id,login_date
数据结果
| id | login_date | date_gap |
|---|---|---|
| 1 | 2024-04-25 | 2024-04-24 |
| 1 | 2024-04-26 | 2024-04-24 |
| 1 | 2024-04-27 | 2024-04-24 |
| 1 | 2024-04-28 | 2024-04-24 |
| 1 | 2024-04-30 | 2024-04-25 |
| 1 | 2024-05-01 | 2024-04-25 |
| 1 | 2024-05-02 | 2024-04-25 |
| 1 | 2024-05-04 | 2024-04-26 |
| 1 | 2024-05-05 | 2024-04-26 |
| 1 | 2024-05-06 | 2024-04-26 |
| 1 | 2024-05-07 | 2024-04-26 |
| 2 | 2024-04-25 | 2024-04-24 |
| 2 | 2024-04-28 | 2024-04-26 |
| 2 | 2024-05-01 | 2024-04-28 |
| 2 | 2024-05-03 | 2024-04-29 |
| 2 | 2024-05-04 | 2024-04-29 |
可以看到连续登录的日期的记录其求解的date_gap是相同的，这时候大多人都能反应过来，求解最长连续登录天数，接下来只要按date_gap分组计数，然后求个最大值就可以了。
3.按date_gap分组计数
select id,date_gap,count(0) as login_day from (
select id,login_date,date_sub(login_date,rank_id) as date_gap from (
select id,login_date
,row_number() over(partition by id order by login_date) as rank_id
from temp.user_login_log
) a
) b
group by id,date_gap
order by id,date_gap
数据结果
| id | date_gap | login_day |
|---|---|---|
| 1 | 2024-04-24 | 4 |
| 1 | 2024-04-25 | 3 |
| 1 | 2024-04-26 | 4 |
| 2 | 2024-04-24 | 1 |
| 2 | 2024-04-26 | 1 |
| 2 | 2024-04-28 | 1 |
| 2 | 2024-04-29 | 2 |
连续登录天数login_day 一目了然了。
4.分组求最长连续登录天数
select id,max(login_day) as max_login_day from (
select id,date_gap,count(0) as login_day from (
select id,login_date,date_sub(login_date,rank_id) as date_gap from (
select id,login_date
,row_number() over(partition by id order by login_date) as rank_id
from temp.user_login_log
) a
) b
group by id,date_gap
) c
group by id
order by id
数据结果
| id | max_login_day |
|---|---|
| 1 | 4 |
| 2 | 2 |
用户1最长登录天数为4天，用户2最长登录天数为2天。
5.衍生问题解答
如果求解的不是最长连续登录天数，而是连续登录n天的所有记录呢？
可以考虑用lead()函数尝试求解。
下期进行完整解答。
以上，本期全部内容。
感谢阅读。
按例，欢迎点击此处关注我的个人公众号，交流更多知识。
