SELECT id, COUNT(*) as [result]
FROM SourceData sd1
WHERE t_o=0 
      AND NOT EXISTS (SELECT 1 
                     FROM SourceData sd2 
                     WHERE sd1.id=sd2.id AND t_o != 0 AND sd2.[Date] > sd1.[Date])
GROUP BY id

演示

首先，我们可以获取每个id的最后一个非零日期。

select id, max(date) as date
from example
where t_o > 0
group by id

这将不会显示4500的值，因为它缺少非零值。
然后，我们可以使用它来选择和分组那些日期之后的值，或者如果id没有非零日期，则选择和分组所有行。

with last_to as(
  select id, max(date) as date
  from example
  where t_o > 0
  group by id
)
select example.id, count(example.t_o)
from example
-- Use a left join to get all ids in example,
-- even those missing from last_to.
left join last_to on last_to.id = example.id
-- Account for the lack of a last_to row
-- if the ID has no non-zero values.
where last_to.date is null
   or example.date > last_to.date
group by example.id

Demonstration.