/*连续登录不少于3天（忽略单日多次登录），新注册，排序：user_id升序；
按id分组，按注册及登录时间排序
1       2.9    1      2.8
1         2.10    2     2.8
1        2.12    3     2.8
2        2.10
2       2.11    */

with 
t1 as (
select user_id,date(log_time) as log_date,row_number()over(partition by user_id order by log_time) as rk
from 
    (select distinct day(log_time),l.*
    from login_tb l) as t
),
t2 as(
    select user_id,log_date,
    date_sub(log_date,interval rk day) as day2
    from t1)

select user_id
from  t2
where user_id in (
     SELECT user_id
     FROM register_tb)
group by user_id,day2
having count(log_date)>=3