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

with 
new_user as (
    select user_id from register_tb where date(reg_time) = '2022-02-08'
)


,user_login as (
    select l.user_id, date(l.log_time) as login_date, row_number() over(partition by l.user_id order by date(l.log_time) asc ) as rk
    from login_tb l where l.user_id in (select distinct user_id from new_user)
)

, login_streak as (
    select user_id, login_date, datediff(login_date, date_add('2022-02-08', interval (rk-1) day)) as days_diff
    from user_login
)

select user_id from login_streak
group by user_id, days_diff
having count(login_date) >= 3
order by user_id asc