SQL刷题-连续3天登陆的用户

连续3天登陆的用户

表a： user_id 用户id、login_date 登陆日期

思路：先取得每个用户登陆日期及后两次登陆日期
若两次登陆日期间隔都为1则为连续登录

with t1 as(select user_id,login_date,lead(login_date,1) over(partition by user_id order by login_date) as lead_1day,lead(login_date,2) over(partition by user_id order by login_date) as lead_2day
from a);

select user_id
from t1
where (lead_1day-login_date)=1 and (lead_2day-lead_1day)=1;

综合：

select t.user_id
from (select user_id,login_date,lead(login_date,1) over(partition by user_id order by login_date) as lead_1day,lead(login_date,2) over(partition by user_id order by login_date) as lead_2day
from a) as t
where (lead_1day-login_date)=1 and (lead_2day-lead_1day)=1;

涉及函数：
lead()、窗口函数
参考：MySQL中LAG()函数和LEAD()函数的使用