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

with
    t1 as (
        select
            user_id,
            date(log_time) log_time
        from
            login_tb
        where
            user_id in (
                select
                    user_id
                from
                    register_tb r
            )
    ),
    t2 as (
        select
            *,
            date_sub(
                log_time,
                interval row_number() over (
                    partition by
                        user_id
                    order by
                        log_time asc
                ) day
            ) subed_series_date
        from
            t1
    ),
    t3 as (
        select
            user_id,
            subed_series_date,
            count(1) `continue_log_days`
        from
            t2
        group by
            user_id,
            subed_series_date
        having
            count(1) > 2
    ),
    t4 as (
        select
            *,
            row_number() over (
                partition by
                    user_id
                order by
                    continue_log_days desc
            ) rk
        from
            t3
    )
select
    user_id
from
    t4
where
    rk = 1