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

with login_dates as (
    select distinct user_id, date(log_time) login_date
    from login_tb
    where user_id in (select user_id from register_tb)
),
tb_2 as (
    select user_id, login_date, row_number()over(partition by user_id order by login_date) rn
    from login_dates
),
tb_3 as (
    select user_id,login_date,date_sub(login_date, interval rn day) grouped_date
    from tb_2
),
tb_4 as(
    select user_id,grouped_date, count(*) as con_days
    from tb_3
    group by user_id, grouped_date
    having con_days >= 3
)

select user_id 
from tb_4