CODEWITHSUNDEEP
sqlpostgresql
George
George

Reputation: 347

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

Upvotes: 0

Views: 506

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 1

Teja
Teja

Reputation: 13534

SELECT the_day,
       amount_raised,
       million_threshold,
       CASE WHEN million_threshold <> lag_million_threshold AND million_threshold = lead_million_threshold
            THEN 1
            WHEN million_threshold = lag_million_threshold
            THEN SUM(million_threshold) OVER ( ORDER BY the_day ROWS UNBOUNDED PRECEDING )
            ELSE 0
        END AS consecutive_day_cnt          
  FROM
     (
       SELECT  the_day, 
               amount_raised,
               million_threshold,
               LAG(million_threshold,1) OVER ( ORDER BY the_day ) AS lag_million_threshold,
               LEAD(million_threshold,1) OVER ( ORDER BY the_day ) AS lead_million_threshold
         FROM
            ( 
              SELECT  the_day,
                      amount_raised,
                      CASE WHEN amount_raised >= 1000000
                           THEN 1
                           ELSE 0
                       END AS million_threshold
                 FROM Yourtable
            )
      );

Upvotes: 0

Related Questions