presto - Counting the number of rows between transitions of values in SQL

Question

I have rows with user_id, timestamps and YES / NO answer. I want to count how many streaks (consecutive rows) of "NO"s each ID has.

Example:

user_id	timestamp	response	no_streak
1	2021-01-20 13:59:26	YES	0
1	2021-01-20 14:01:27	NO	1
1	2021-01-20 14:03:21	NO	2
1	2021-01-20 14:07:29	NO	3
1	2021-01-20 14:09:22	YES	0
1	2021-01-20 14:11:26	YES	0
1	2021-01-20 14:13:30	NO	1
1	2021-01-20 14:17:26	NO	2
1	2021-01-20 14:19:29	YES	0
1	2021-01-20 14:25:30	NO	1
1	2021-01-20 14:27:23	NO	2
1	2021-01-20 14:31:23	NO	3
1	2021-01-20 14:35:27	NO	4
1	2021-01-20 14:39:24	YES	0
2	2021-01-20 14:39:24	NO	1
2	2021-01-20 14:47:28	NO	2
2	2021-01-20 14:49:22	NO	3
2	2021-01-20 14:51:25	NO	4
2	2021-01-20 14:53:29	NO	5
2	2021-01-20 14:55:22	NO	6
2	2021-01-20 14:57:22	YES	0

question from:https://stackoverflow.com/questions/65945841/counting-the-number-of-rows-between-transitions-of-values-in-sql

Answer 1

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;