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?
and here is the desired output
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