请注意，日期之间的任何间隔将被视为连续天数。

with data as (
    select id, date,
        case when min(balance) >= 0 then 0 else 1 end as tally,
        sum(case when min(balance) >= 0 then 1 else 0 end)
            over (partition by id order by date) as grp
    from t
    group by id, date
)
select id, date,
    sum(tally) over (partition by id, grp, tally order by date) as running_days
from data
order by id, date;

若要将缺少的日期视为非连续日期，请尝试：

sum(case when min(balance) >= 0 then 1 else 0 end)
        over (partition by id order by date) +
    datediff(day, min(date) over (partition by id), date) -
    row_number() over (partition by id order by date) + 1 as grp

https://rextester.com/nkbzg48737

这是一种与过滤有关的间隙和孤岛问题。这里有一种方法：

select t.*,
       row_number() over (partition by id, dateadd(day, - seqnum, date)
                          order by date
                         ) as Number_of_Consecutive_Days
from (select t.id, date, min(balance) as min_balance,
             row_number() over (partition by id order by date) as seqnum
      from t
      group by t.id, date
      having min(balance) < 0
     ) t;

这是通过计算只有负余额的天数来实现的。然后从日期中减去一个序列号。在相邻的几天里，这是不变的——因此在外部的差异 row_number() .
编辑：
如果您只想计算截至给定日期任何负余额的天数，可以使用：

select t.*,
       sum(case when min_balance < 0 then 1 else 0 end) over (partition by id order by date) as Number_of_Consecutive_Days
from (select t.id, date, min(balance) as min_balance
      from t
      group by t.id, date
     ) t;