【SQL】连续登录天数

连续登录天数的SQL查询
数据库版本:MySQL 8.0.16

建表以及插入数据

CREATE TABLE tmp_last_3_days (
  user_id int(11) ,
  login_date date 
) COMMENT='连续登录表' ;
insert into tmp_last_3_days values(123,'2020-05-25');
insert into tmp_last_3_days values(123,'2020-05-26');
insert into tmp_last_3_days values(123,'2020-05-27');
insert into tmp_last_3_days values(456,'2020-05-23');
insert into tmp_last_3_days values(456,'2020-05-25');
insert into tmp_last_3_days values(789,'2020-05-21');
insert into tmp_last_3_days values(789,'2020-05-25');
insert into tmp_last_3_days values(789,'2020-05-26');
insert into tmp_last_3_days values(789,'2020-05-27');
insert into tmp_last_3_days values(789,'2020-05-22');
insert into tmp_last_3_days values(789,'2020-05-23');

1-连续三天登录

select distinct user_id,login_date
from (select user_id,login_date,
lead(login_date,2,-1)over(partition by user_id order by login_date desc) as lead_date
from tmp_last_3_days )T 
where date_sub(login_date,interval 2 day)=lead_date
order by login_date;
+---------+------------+
| user_id | login_date |
+---------+------------+
|     123 | 2020-05-27 |
|     789 | 2020-05-27 |
|     789 | 2020-05-23 |
+---------+------------+
3 rows in set, 6 warnings (0.00 sec)

2-最近连续登录几天

insert into tmp_last_3_days values(789,'2020-05-28');
insert into tmp_last_3_days values(789,'2020-05-24');

select user_id,min_login_date,last_day_num
from (
select user_id,min_login_date,last_day_num,
row_number()over(partition  by user_id order by min_login_date desc) as rn
from (
select user_id,min(login_date) as min_login_date,count(first_day) as last_day_num
from (
select user_id,login_date,
row_number()over(partition by user_id order by login_date) as day_num,
date_sub(login_date,interval row_number()over(partition by user_id order by login_date) day) as first_day
from tmp_last_3_days ) T 
group by user_id,first_day having count(1)>1
)A
)B 
where B.rn=1;
+---------+----------------+--------------+----+
| user_id | min_login_date | last_day_num | rn |
+---------+----------------+--------------+----+
|     123 | 2020-05-25     |            3 |  1 |
|     789 | 2020-05-21     |            8 |  1 |
+---------+----------------+--------------+----+
2 rows in set (0.01 sec)