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

select
    user_id
from 
(
    select 
        user_id,
        login_date,
        row_number() over (partition by user_id order by login_date) as rk,
        date_sub(login_date, interval row_number() over (partition by user_id order by login_date) day) as date_same
    from 
    (
        -- 首先获取去重的登录日期
        select distinct
            user_id,
            date(log_time) as login_date
        from 
            login_tb
        where 
            user_id in (select user_id from register_tb)
    ) distinct_dates
) as t
group by 
    user_id, date_same
having 
    count(*) >= 3;  -- 连续登录的天数>=3