题解 | #查询连续登陆的用户#

select distinct tmp1.user_id user_id from
(
    select lt.user_id user_id,date_add(date(log_time),interval 2 day) next_day,
    lead(date(log_time),2,'2022-01-01') over (partition by lt.user_id order by log_time) next_login
    from login_tb lt left join register_tb rt on lt.user_id=rt.user_id
) tmp1
where next_day=next_login and tmp1.user_id in (
    select user_id from register_tb)

针对查询连续登录类问题，使用date_add及lead函数解决。如连续两天登录，那就判断date_add(登录日期,interval 1 day) as next_day=lead(登录日期,1,'2022-01-01') as next_login(其中2022-01-01是默认日期，不用0是因为会让这个变量变成变成字符串格式)。

同理，如果是连续三天，就判断date_add(登录日期,interval 2 day) as next_day=lead(登录日期,2,'2022-01-01') as next_login