George George - 1 year ago 66
SQL Question

SQL find consecutive days of specific threshold reached

I have two columns; the_day and amount_raised. I want to find the count of consecutive days that at least 1 million dollars was raised. Am I able to do this in SQL? Ideally, I'd like to create a column that counts the consecutive days and then starts over if the 1 million dollar threshold is not reached.

What I've done thus far is create a third column that puts a 1 in the row if 1 million was reached. Could I create a subquery and count the consecutive 1's listed, then reset when it hits 0? enter image description here

and here is the desired output

enter image description here

vkp vkp
Answer Source
select dt,amt,
case when amt>=1000000 then -1+row_number() over(partition by col order by dt) 
else 0 end col1
from (select *, sum(case when amt >= 1000000 then 0 else 1 end) over(order by dt) col 
      from t) x

Sample Demo