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

Answer 1

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;