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

with
    t1 as (
        select
            user_id,
            log_time,
            row_number() over (
                partition by
                    user_id
                order by
                    log_time
            ) rk
        from
            login_tb
        where
            user_id in (
                select
                    user_id
                from
                    register_tb
            )
    ),
    t2 as (
        select
            user_id,
            date(date_sub(log_time, interval rk day)) grp
        from
            t1
    )
select
    user_id
from
    (
        select
            user_id,
            count(grp) log_days
        from
            t2
        group by
            user_id,
            grp
        having
            log_days >= 3
    ) b