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

select user_id
from(
    select user_id, max(rk)-min(rk)+1 连续登录
    from(
        select user_id, log_time,
        row_number() over(partition by user_id order by log_time) rk,
        date_sub(date_format(log_time,'%Y-%m-%d'), interval row_number() over(partition by user_id order by log_time) day) 初始日期
        from login_tb
    ) as t1
    group by 初始日期,user_id
) as t2
where user_id in(
    select distinct user_id from register_tb
)
group by user_id
having max(连续登录) >= 3
order by user_id