Identify three consecutive rows with values greater than a given number

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 IdTest DateScore
111122225/6/20187
111122225/9/20188
111122225/10/201810
111122225/12/201811
111122225/13/201812
111122225/15/20186
111122225/17/20183
111133336/15/20189
111133336/16/201810
111133336/20/20184
111133336/28/20188
111133337/2/20189
111133337/5/201811
111133337/9/20185

And add a column that does this:

Customer IdTest DateScoreScore_Qualifier
111122225/6/20187N
111122225/9/20188N
111122225/10/201810N
111122225/12/201811Y
111122225/13/201812Y
111122225/15/20186N
111122225/17/20183N
111133336/15/20189N
111133336/16/201810N
111133336/20/20184N
111133336/28/20188N
111133337/2/20189N
111133337/5/201811Y
111133337/9/20185N

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
Teradata Employee

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