Count the number of consecutive Zeros along with their instances SQL

Question

Data is as follows:

Row_No    ID     Bal
1        01      0
2        01      0
3        01      0
4        01      10
5        01      0
6        01      0
7        01      20
8        01      0
9        02      30
10       02      0
11       02      40
12       02      10
13       02      0
14       02      25
15       02      0
16       02      0
17       02      0

instances = number of times consecutive zeros

count(consecutive_zeros) = total zeros

For ID = 01:

Instances:

Instance no 1: Row_No 1,2,3

Instance no 2: Row_No 5,6

count(consecutive_zeros):

For instance no 1 we have 3 zeros

For instance no 2 w have 2 zeros

Total = 5

Row_no 8 is not considered as it's not followed by consecutive zeros

Required Output

ID    instances    count(consecutive_zeros)
01    2            5 
02    1            3

Tag your question with the database you are using. – Gordon Linoff Jun 16 '21 at 11:34 — Gordon Linoff, Jun 16 '21 at 11:34

score 1 · Accepted Answer · answered Jul 15 '21 at 20:22

Gordin Linoff, your answer is nearly perfect. However, this will give the accurate results:

select  id
        ,BAL_CON_ZERO_INSTANCES+ (total_zeros-BAL_CON_ZERO_DAYS) AS BAL_CON_ZERO_INSTANCES
        ,BAL_CON_ZERO_DAYS
(
select id,

        ,sum(case when bal = 0 and (prev_bal = 0 or prev_bal is null) and (next_bal = 1 or next_bal is null) 
                then 1 else 0
            end)  as BAL_CON_ZERO_INSTANCES
        ,sum(case when (bal = 0 and (next_bal = 0 or next_bal is null) or (bal = 0 and (prev_bal = 0 or prev_bal is null) and (next_bal = 1 or next_bal is null))) then 1 else 0 end) as BAL_CON_ZERO_DAYS
        ,sum(case when bal = 0 then 1 else 0 end) as total_zeros

from (select t.*,
             bal,
             lag(bal) over (partition by id order by row_no) as prev_bal,
             lead(bal) over (partition by id order by row_no) as next_bal
      from t
     ) t
group by id;

This answer produces the correct outcome. However, I think you are missing a `from` before the first subquery (line 4) and also an end parenthesis at the end (might need to remove / move the last semi-colon too). — Landscape, Mar 22 '23 at 11:02

score 0 · Answer 2 · answered Jun 16 '21 at 11:33

;For this, you can use lag() and lead():

select id,
       sum(case when bal = 0 and (prev_bal = 0 or prev_bal is null) and
                     next_bal = 0
                then 1 else 0
            end) as instances,
       sum(case when bal = 0 and prev_bal = 0 then 1 else 0 end)
from (select t.*,
             lag(bal) over (partition by id order by row_no) as prev_bal,
             lead(bal) over (partition by id order by row_no) as next_bal
      from t
     ) t
group by id;

You do not have to treat this as a gaps-and-islands problem. Your two measures are pretty simple:

instances counts when the value "changes" to 0 and the next value is 0 as well.
count counts the values where the value is 0 and the next value is 0 as well.

Count the number of consecutive Zeros along with their instances SQL

2 Answers2