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

with t1 as(
select 
    b.user_id,
    b.log_time,
    lag(log_time) over(partition by b.user_id order by b.log_time
    rows between 1 preceding and current row) up_log_time
from register_tb a join login_tb b on a.user_id = b.user_id),

t2 as(
select
     * ,
    timestampdiff (day, date_format(t1.up_log_time,'%Y-%m-%d'), date_format(t1.log_time,'%Y-%m-%d')) day_diff
from t1 where t1.up_log_time is not null),

t3 as(
select 
    t2.user_id,
    count(1) cnt
from t2 
where day_diff = 1 
    group by t2.user_id
    having cnt >=2
    )
select t3.user_id from t3 order by t3.user_id;