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

with a as(
select
    l.user_id as user_id,
    substring(log_time,1,10) as time,
    row_number() over(partition by user_id) as datediff
from login_tb l
join register_tb r
on l.user_id=r.user_id
group by user_id,log_time
),
b as(
select 
    user_id,
    date_sub(time,interval datediff day) as date,
    count(*) as count
from a
group by user_id,date_sub(time,interval datediff day)
)

select
    user_id
from b
where count >= 3
order by user_id
;