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

select user_id
from (
    select reg.user_id, log_time,
    date_sub(date(log_time), interval row_number() over (partition by reg.user_id order by log_time) day) as diff
    from register_tb reg join (
        select user_id, log_time
        from login_tb
        group by user_id, log_time
    ) login
    on reg.user_id=login.user_id and reg.reg_time<login.log_time
) temp
group by user_id, diff
having count(1)>=3
order by user_id

计算diff前，需要转换下时间格式，date(log_time)