Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

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

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