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

select user_id
from(
    select  t1.user_id , substr(log_time,1,10) as dt
            ,row_number()over(partition by user_id order by log_time) as rn 
    from login_tb t1 
    inner join register_tb t2 on t1.user_id = t2.user_id 
)t
group by user_id , date_sub(dt, INTERVAL  rn day)
having count(1) >=3

开窗计算各个用户日期升序序号rn， 连续登录时date_sub(dt, INTERVAL rn day)相同，count就是连续登录天数