Hello I've been searching the forums high and low and can't seem to find a solution to my problem. I have a set of data that contains a list of customers and their scores for a certain test. I need to identify the instances where there are 3 consecutive scores (ordered by date of test) greater than 7 or one score greater than 12. The greater than 12 part is easy but I can't figure out the 3 consecutive scores greater than 7, I've found solutions that identify if 3 rows have the same score but nothing that identifies 3 rows greater than a given number. So I'd need to take a dataset that looks like this:
|Customer Id||Test Date||Score|
And add a column that does this:
|Customer Id||Test Date||Score||Score_Qualifier|
In this example the score_qualifier is Y beginning with the 3rd consecutive value greater than 7 but it could also be Y beginning with the 1st value so long as the 3rd value is Y as well. Any help would be greatly appreciated!
You didn't show what you got so far...
Take a look at: MIN(Score) OVER (PARTITION BY CustomerId ORDER BY TestDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) >= 7
MAX(Score) OVER (PARTITION BY CustomerId ORDER BY TestDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) >= 12