sum of consecutive values

I have following data in a Teradata table. My requirement is a consecutive sum between OFFLOAD_SECONDS <5 MInutes(300seconds) and ignore the OFFLOAD_SECONDS value where OFFLOAD_SECONDS >=5Minutes and do the rolling sum until we get offload_seconds >=5 minutes.

My Current Data.

DATE        number     Time                    OFFLOAD_SECONDS     
2019/03/14     461     2019/03/14 18:24:53     ?     
2019/03/14     461     2019/03/14 18:25:32     39     
2019/03/14     461     2019/03/14 18:32:31     419
2019/03/14     461     2019/03/14 18:32:35     4     
2019/03/14     461     2019/03/14 18:32:52     17     
2019/03/14     461     2019/03/14 18:33:00     8     
2019/03/14     461     2019/03/14 18:33:08     8     
2019/03/14     461     2019/03/14 18:45:53     765 

Output

Date        Number   consecutive sum of OFFLOAD_SECONDS 
3/14/2019   461      39
3/14/2019   461      37

Answer

This query should give you expected output. Let me know.

http://sqlfiddle.com/#!17/4a4fb/3

    select distinct date
      , sum(case when offload_seconds>=300 then 0 else offload_seconds end) over (partition by col_sum)  as con_sum
    from 
    (
      select date, number, time, offload_seconds
      , sum(case when offload_seconds>=300 then 1 else 0 end) over( rows between unbounded preceding and current row) as col_sum
      from tab1
    ) a
    qualify con_sum<>0

Alternatively, this longer version with extra select wrapping the above SQL, also works:

http://sqlfiddle.com/#!17/4a4fb/13

 select dt, nm, con_sum 
 from (

    select distinct dt, nm
      , sum(case when offload_seconds>=300 then 0 else offload_seconds end) over (partition by col_sum)  as con_sum
    from 
    (
      select dt, nm, tm, offload_seconds
      , sum(case when offload_seconds>=300 then 1 else 0 end) over( rows between unbounded preceding and current row) as col_sum
      from tab2
    ) a
) t
where t.con_sum <>0;