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

with t1 as(
select 
    lt.user_id,
    date_format(log_time,'%Y-%m-%d') log_date,
    dense_rank()over(partition by lt.user_id order by date_format(log_time,'%Y-%m-%d')) drn
from register_tb rt join login_tb lt using(user_id)
),
t2 as(
select 
    user_id,
    log_date,
    date_sub(log_date,interval drn day) diff_date
from t1
)
select 
    distinct user_id
from t2 group by user_id,diff_date having count(distinct log_date)>=3
order by user_id