Finding when a rolling group of day's values exceed a threshold

user_15711452 0
Viewed 44

I am looking to create a flag for when 3 consecutive days (looking backwards) exceed a value (let's say 5).

date   value active_flag
202101 1     0
202102 2     0
202103 1     0
202104 6     0
202105 9     0
202106 7     1
202107 1     0
202108 4     0

Apologies for lack of code as I am relatively new to SQL so am not sure of how to show this in a window function but I was thinking of counting number of days exceeding value = 3 to return this flag

sql-server sql
1 Answers

We can use COUNT here along with a window of the two preceding and current rows:

SELECT date, value,
       CASE WHEN COUNT(CASE WHEN value > 5 THEN 1 END) OVER
       (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3
            THEN 1 ELSE 0 END AS active_flag
FROM yourTable
ORDER BY date;

screen capture from demo link below

Demo

user_18632290
Related