Count the number of "yes"s up to each row so the adjacent NOs have the same grouping value. Then filter and aggregate:
select t.user_id, count(*), min(timestamp), max(timestamp)
from (select t.*,
sum(case when response = 'YES' then 1 else 0 end) over (partition by user_id order by timestamp) as grp
from t
) t
where response = 'NO'
group by user_id, grp;
Note: This doesn't return streaks of 0
length. I'm not sure of "streak" is the right word for that. But to get them, remove the where
filter and use conditional aggregation:
select t.user_id, sum(case when response = 'NO' then 1 else 0 end),
min(timestamp), max(timestamp)
from (select t.*,
sum(case when response = 'YES' then 1 else 0 end) over (partition by user_id order by timestamp) as grp
from t
) t
group by user_id, grp;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…