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

with a1 as(
    select user_id,date(log_time) as base_date ,
    rank() over(partition by user_id order by date(log_time)) as rk
    from login_tb
    where user_id in (select user_id from register_tb)
    group by user_id,date(log_time)
),

a2 as(
    select *,date_sub(base_date,interval rk day ) as u
    from a1
),

a3 as(
    select user_id,u,count(*) as con_days
    from a2
    group by user_id,u
)

select user_id
from a3
where con_days > 2
order by user_id