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

with user_login as (
  select 
    rt.user_id, 
    lt.log_time,
    -- 取后一天的登录时间
    lead(lt.log_time, 1) over(partition by rt.user_id order by lt.log_time) as next_day,
    -- 取后两天的登录时间
    lead(lt.log_time, 2) over(partition by rt.user_id order by lt.log_time) as next_next_day
  from register_tb rt
  join login_tb lt on rt.user_id = lt.user_id
) 
select distinct user_id
from user_login
-- 确保存在连续三天：当天与次日差1天，次日与后日差1天
where datediff(next_day, log_time) = 1 
  and datediff(next_next_day, next_day) = 1
order by user_id;