sqlite count 1 value based on all previous row and restart count when row value is 0

Question

I have a sqlite column with 0 and 1 values and I need to get the max consecutive 1 value. thanks for your help

sqlite> SELECT data_suites  from my_table ;
0
0
1
1
1 <--- 3 consecutive 1
0
1
1
1
1 <--- 4 consecutive 1
0
1
1
1
1
1
1 <---  6 consecutive 1

I am expecting to get

6

Answer 1

Tables are unordered set so you need a column to indicate sorting(id, update_time, ...). Once you have it you could use windowed functions:

WITH cte AS (
 SELECT *,ROW_NUMBER() OVER(ORDER BY id)-ROW_NUMBER() OVER(PARTITION BY data_suites ORDER BY id) grp
 FROM my_table
)
SELECT COUNT(*) cnt
FROM cte
WHERE data_suites = 1
GROUP BY grp
ORDER BY cnt DESC LIMIT 1;
-- answer 6

db<>fiddle demo

---

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(ORDER BY id) - ROW_NUMBER() OVER(PARTITION BY data_suites ORDER BY id) grp
  FROM my_table
)
SELECT grp,MIN(id) AS start, MAX(id) AS end, COUNT(*) cnt
FROM cte
WHERE data_suites = 1
GROUP BY grp;

db<>fiddle demo2

Output:

+------+--------+------+-----+
| grp  | start  | end  | cnt |
+------+--------+------+-----+
|   2  |     3  |   5  |   3 |
|   3  |     7  |  10  |   4 |
|   4  |    12  |  17  |   6 |
+------+--------+------+-----+