SQL语句查询连续N天登录用户(解决方案)

drop table if exists t_login_records;#若表存在删除
-- 创建用户登录记录表
create table t_login_records (
    id int primary key auto_increment,
    user_id int not null,
    login_date date not null
)engine=innodb default charset=utf8 collate=utf8_unicode_ci comment='用户登录记录表';

-- 查看表结构	
desc t_login_records;

insert into t_login_records (user_id, login_date) values
(1, '2023-01-01'),
(1, '2023-01-02'),
(1, '2023-01-03'),  -- 用户1连续3天登录
(1, '2023-01-05'),
(2, '2023-01-01'),
(2, '2023-01-02'),
(2, '2023-01-04'),  -- 用户2不连续
(3, '2023-01-01'),
(3, '2023-01-02'),
(3, '2023-01-03'),  -- 用户3连续3天登录
(3, '2023-01-04'),  -- 用户3连续4天登录
(4, '2023-01-01'),
(4, '2023-01-03'),
(4, '2023-01-05'),  -- 用户4不连续    
(5, '2023-01-01'),
(5, '2023-01-02'),
(5, '2023-01-03'),  -- 用户5连续3天登录
(5, '2023-01-04'),  -- 用户5连续4天登录
(5, '2023-01-05'),	-- 用户5连续5天登录
(5, '2023-02-01'),	-- 断开
(5, '2023-02-02'),  -- 用户5再连续2天登录
(5, '2023-02-03');  -- 用户5再连续3天登录
select * from t_login_records;

-- 方法1：自连接查询
select distinct t1.user_id
from t_login_records t1
join t_login_records t2 
  on t1.user_id = t2.user_id 
  and datediff(t2.login_date, t1.login_date) = 1
join t_login_records t3 
  on t1.user_id = t3.user_id 
  and datediff(t3.login_date, t1.login_date) = 2;

-- 方法2：窗口函数（适用于支持lead函数的数据库，如mysql 8.0+、postgresql）
select distinct user_id
from (
  select 
    user_id,
    login_date,
    lead(login_date, 1) over (partition by user_id order by login_date) as next_day,
    lead(login_date, 2) over (partition by user_id order by login_date) as next_2_days
  from t_login_records
) t
where datediff(next_day, login_date) = 1 
  and datediff(next_2_days, login_date) = 2;

-- 方法3：日期差值分组（适用于支持row_number的数据库）
select user_id
from (
  select 
    user_id,
    login_date,
    date_sub(login_date, interval row_number() over (partition by user_id order by login_date) day) as grp
  from t_login_records
) t
group by user_id, grp
having count(distinct login_date) >= 3;