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

select
    tt.user_id
from
    (select
        t.user_id,
        t.start_date,
        max(date_rank) - min(date_rank) + 1 as days
    from
        (select
            rt.user_id,
            date(lt.log_time) as log_date,
            row_number() over(partition by rt.user_id order by date(lt.log_time)) as date_rank,
            date_sub(date(lt.log_time),interval row_number() over(partition by rt.user_id order by date(lt.log_time)) day) as start_date
        from
            register_tb rt
        join 
            login_tb lt on rt.user_id = lt.user_id) t
    group by 
        1,2   ) tt
where
    days >= 3