Count the number of Consecutive 0s in a column with SQL

Question

I have a table that contains following data:

sample data

It has balance containing 0s and 1s. Required is the total number of consecutive 0s as well the count of consecutive instances, as below.

output

Answer 1

You can assign a grouping to the 0s by doing a cumulative sum of 1s. The rest is aggregation. So, for each sequence of 0s:

select min(dt), max(dt), count(*)
from (select t.*,
             sum(bal) over (order by dt) as grp
      from t
     ) t
where bal = 0
group by grp;

I'm not sure how you want to summarize this, but this solves the "hard" part of the problem.