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

with T1 as(
    select l.user_id as user_id,
    date(l.log_time) as logdate1,
    lead(date(l.log_time),1)over(partition by l.user_id order by l.log_time) as logdate2,
    lead(date(l.log_time),2)over(partition by l.user_id order by l.log_time) as
logdate3
    from login_tb l

)

select t.user_id
from T1 t 
where datediff(t.logdate2,t.logdate1)=1 
and datediff(t.logdate3,t.logdate2)=1
and t.user_id in (select distinct r.user_id from register_tb r)