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

with t1 as (
    select user_id,date(log_time) log_time,row_number()over(partition by user_id order by date(log_time)) as rk
    from login_tb
    group by 
    user_id,date(log_time)
)
,t2 as (
    select *,date_sub(log_time,interval rk day) as t2
    from t1
)
,t3 as (
    select user_id,t2,count(distinct log_time) as nd
    from t2
    group by user_id,t2
    having count(distinct log_time) >=3
    )
select b.user_id
from t3 a
inner join 
(select user_id,date(reg_time) reg_time from register_tb) b  
on a.user_id = b.user_id