SQL query for total number of continuous item check

Question

SQLITE/SQL Query for total counts of continuous records, PFA image. Here I have 1 and 0 in table. Suppose I want to check the total number of 1 which are exactly in multiple of 3 or you can say countinuous. If zero apperas than it will not considerd.

Example A:- (Column values displayed horizontally)

1 1 1 1 0 0 0 0 0 1 1 1 0 0 0 1 1 1 0 1 1 0 1 0 1 1 1 1 1 1 1 1 0 1 1 0 1 1 1 1 1 1 1 1 1 1 0 1 1 

So the total count of occurance is 8 any sequency divide by 3.

[EDIT 2] Consider first column as milliseonds and its in assending order

Answer 1

Here's an attempt to answer your question, considering that we can use the deviceid to order the rows and count the sequences

note that in order to work, you should have sqlite version 3.25 at least, because this requires window functions

Creating a test table with an auto-increment deviceid column, that will simulate your own data

CREATE TABLE "test" (
    "deviceid"  INTEGER,
    "password"  INTEGER,
    PRIMARY KEY("deviceid" AUTOINCREMENT)
);

inserting rows following your own sequence 1 1 1 1 0 0 0 0 0 1 1 1 0 0 0 1 1 1 0 1 1 0 1 0 1 1 1 1 1 1 1 1 0 1 1 0 1 1 1 1 1 1 1 1 1 1 0 1 1

DELETE FROM test;
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (0); 
INSERT INTO test (password) VALUES (1); 
INSERT INTO test (password) VALUES (1);

Solution :

select password, sum(div_floor) AS Number_of_3_occurences
FROM (
    select password, count(*) as cnt_sequence, cast(round(count(*) / 3 ) as int) as div_floor
    from (select test.*,
                 (row_number() over (order by deviceid) -
                  row_number() over (partition by password order by deviceid)
                 ) as grp
          from test
         ) t
    group by grp, password
) ttt
where  cnt_sequence>=3
group by password 

Result

password  Number_of_3_occurences
0         2
1         8