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)