Database
Visitor

## Identify three consecutive rows with values greater than a given number

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 11112222 5/6/2018 7 11112222 5/9/2018 8 11112222 5/10/2018 10 11112222 5/12/2018 11 11112222 5/13/2018 12 11112222 5/15/2018 6 11112222 5/17/2018 3 11113333 6/15/2018 9 11113333 6/16/2018 10 11113333 6/20/2018 4 11113333 6/28/2018 8 11113333 7/2/2018 9 11113333 7/5/2018 11 11113333 7/9/2018 5

And add a column that does this:

 Customer Id Test Date Score Score_Qualifier 11112222 5/6/2018 7 N 11112222 5/9/2018 8 N 11112222 5/10/2018 10 N 11112222 5/12/2018 11 Y 11112222 5/13/2018 12 Y 11112222 5/15/2018 6 N 11112222 5/17/2018 3 N 11113333 6/15/2018 9 N 11113333 6/16/2018 10 N 11113333 6/20/2018 4 N 11113333 6/28/2018 8 N 11113333 7/2/2018 9 N 11113333 7/5/2018 11 Y 11113333 7/9/2018 5 N

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!

2 REPLIES

## Re: Identify three consecutive rows with values greater than a given number

Homework, huh?

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

Cheers.

Carlos

Junior Supporter

## Re: Identify three consecutive rows with values greater than a given number

Adding to what Carlos has mentioned and assuming you need all three rows and not just the third row do the below

Select *
From tableA
Qualify min(score) over(partition by customerid order by testdate rows between 2 preceding and current row) >= 7
Or min(score) over(partition by customerid order by testdate rows between 1 preceding and 1 following) >= 7
Or min(score) over(partition by customerid order by testdate rows between current row and 2 following) >= 7