SQL面试题每日一题之求解用户最长连续登录天数

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()函数尝试求解。

下期进行完整解答。

以上，本期全部内容。

感谢阅读。

按例，欢迎点击此处关注我的个人公众号，交流更多知识。