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

with temp as (

    select l.user_id,reg_time,log_time,row_number() over (

     partition by l.user_id

     order by log_time

     ) as rn

    from login_tb l

    left join register_tb r

    on l.user_id = r.user_id

    order by l.user_id ASC

)

select user_id

from(

    select temp.user_id,date(temp.log_time) - interval temp.rn day as date1,count(1) as cn

    from temp

    where date(temp.reg_time) >= "2022-02-08"

    group by user_id,date1

) t

where cn >=3