How to find consecutive non zero values from a column I have a table as follows
user  timestamp            counts   
xyz   01-01-2020 00:05:00   12
xyz   01-01-2020 00:10:00   11
xyz   01-01-2020 00:15:00   45
xyz   01-01-2020 00:20:00   0
xyz   01-01-2020 00:25:00   0
xyz   01-01-2020 00:30:00   13
xyz   01-01-2020 00:35:00   12
xyz   01-01-2020 00:40:00   0

I want to slice it based on max consecutive values(non-zero) values in count column and find out start timestamp, end timestamp and total_count in that duration
starttime            endtime               total_count
01-01-2020 00:05:00  01-01-2020 00:15:00   68

How can I achieve this using BigQuery
Test something like
WITH cte AS ( SELECT *, SUM(CASE WHEN counts=0 THEN 1 ELSE 0 END) OVER (ORDER BY timestamp) grp
              FROM sourcetable )
SELECT MIN(timestamp) start_time, MAX(timestamp) end_time, SUM(counts) total_count
FROM cte
WHERE grp = 0

DEMO fiddle (MySQL 8 used)