How to select sets of consecutive three values with a specific filter

Viewed 73

Having customers and orders they made, I am trying to get all sets of three consecutive values of a column based on the date.

For instance, it should return the below for a customer


-------------+------------+------------------------------+
| CustomerID |  OrderType |         OrderDate            |       
-------------+------------+------------------------------+
| 1010       |    193     |     2022-09-10 09:54:04.380  |
| 1010       |    130     |     2022-09-09 09:04:21.120  |
| 1010       |    125     |     2022-09-05 00:00:55.947  |
-------------+------------+------------------------------+

It should not return these ones

-------------+------------+------------------------------+
| CustomerID |  OrderType |         OrderDate            |       
-------------+------------+------------------------------+
| 1020       |    193     |     2022-09-10 09:54:04.380  |
| 1020       |    185     |     2022-09-08 10:04:21.120  |
| 1020       |    130     |     2022-09-03 10:13:25.597  |
| 1020       |    125     |     2022-09-01 01:00:15.523  |
-------------+------------+------------------------------+

OrderType returned should be only these ones: 125, 130, 193

I tried this one but not sure how to change it to return correct rows

SELECT  tmp.CustomerID,  tmp.OrderType, tmp.OrderDate
FROM   ( SELECT  O.CustomerID, O.OrderType, O.OrderDate,    
                      ROW_NUMBER() OVER (PARTITION BY O.CustomerID
                                  ORDER BY O.OrderDate desc, O.OrderType DESC
                                 ) AS rn
             
            FROM Orders O
            WHERE O.OrderType in (125, 130, 193) 
        ) tmp
            
        WHERE tmp.rn <= 3  
        ORDER BY tmp.CustomerID DESC, tmp.rn DESC 
0 Answers
Related