select
user_id,
max(days)
from (
select user_id,
f,
count(1) days
from (
select user_id,
login_time,
sum(if(flag > 2 , 1, 0)) over (partition by user_id order by login_time) f
from (
select user_id,
login_time,
lag(login_time, 1, '1970-01-01') over (partition by user_id order by login_time) last_login_time,
datediff(login_time,lag(login_time, 1, '1970-01-01') over (partition by user_id order by login_time)) flag
from login
) t1
group by user_id, login_time
) t2
group by user_id, f
) res
group by user_id
11-02
243
07-08
856
03-22
760