(SQLite) Count number of times a value repeats 3 times continuously

I understand that this is a complex query, thus I have a separate column for this, but the column contains almost 99% values as null (which is very ineffecient), thus I want to know it its possible to do this through a query.

I have table something like this:

TransactionId | Value | ProductId
      1           3         2
      2           2         2           
      3           1         2
      4           0         4
      5           0         4
      6           0         4
      7           3         7
      8           3         7
      9           1         7
     10           0         3
     11           0         3
     12           0         3
     13           5         1
     14           2         1
     15           3         1
     16           0         4
     17           0         4
     18           0         4

Now the query is, that if for 3 consecutive product ids, the corresponding value is 0 (in order of TransactionId ASC), then it will be counted as 1 for ProductId i.e.

ProductId | Count 
    4         2
    3         1

How can we query this?

An optional short question :p

Is it alright if I have columns which have mostly null values?

Answer

In the more recent versions of SQLite, you can use lag()/lead():

select product_id, count(*)
from (select t.*,
             (case when value = 0 and
                        lead(value, 1) over (partition by productid order by transactionid) = 0 and
                        lead(value, 2) over (partition by productid order by transactionid) = 0
                   then 1
              end) as three_zero_flag
      from t
     ) t
where three_zero_flag = 1
group by product_id