select
    t3.user_id
from
(
    select
        user_id,
        count(*) as cut
    from
    (
        select
            user_id,
            date_sub(log_date,interval rank_ day) as sub_date
        from
        (
            select
                user_id,
                date(log_time) as log_date,
                row_number() over(partition by user_id order by date(log_time)) as rank_
            from
                login_tb
            ) t1
        ) t2
    group by
        user_id,sub_date       
    ) t3
join 
register_tb r on t3.user_id = r.user_id
where
    t3.cut>=3

# with recursive cte as(
#     select
#         user_id,
#         date(log_time) as log_date,
#         1 as depth
#     from
#         login_tb
#     union all
#     select
#         l.user_id,
#         date(l.log_time) as log_date,
#         c.depth + 1 as depth
#     from
#         login_tb l
#     join 
#         cte c on l.user_id = c.user_id
#     where
#         date_sub(date(l.log_time),interval 1 day) = c.log_date 
# )

# select
#     c.user_id
# from
#     cte c 
# join
#     register_tb r on c.user_id = r.user_id
# where
#     date(r.reg_time) = '2022-02-08'
# group by
#     c.user_id
# having max(depth) >= 3
# order by c.user_id

# select
#     t2.user_id
# from
#     (
#     select
#         t1.user_id,
#         t1.log_date,
#         sum(case when date_sub(t1.log_date,interval 1 day) = lag_ then 0 else 1 end)  over(partition by user_id order by t1.log_date) as sum_
#     from
#         (
#         select
#             user_id,
#             date(log_time) as log_date,
#             lag(date(log_time)) over(partition by user_id order by date(log_time)) as lag_
#         from
#             login_tb
#         ) t1 
#     ) t2
# join
#     register_tb r on r.user_id = t2.user_id
# where date(r.reg_time) = '2022-02-08'
# group by t2.user_id,t2.sum_
# having count(*) >=3
# order by t2.user_id