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

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

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