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

-- 连续登陆不少于3天的新注册用户
with t as (select user_id, date(log_time) as log_date, 
row_number() over (partition by user_id order by log_time) as rn
from login_tb ),

t2 as (select user_id, log_date, date_sub(log_date, interval rn day) as day
from t),

t3 as (select t2.user_id, t2.log_date, t2.day  
from t2 left join register_tb r on t2.user_id = r.user_id
where r.reg_time <= t2.log_date),


t4 as( select user_id, count(*) as num_consec
from t3
group by user_id, day
having  count(*) >= 3)

select user_id from t4 order by user_id