Finding all matching rows in a 30 day time window

Question

I have a situation where I have a row in a table for each time a customer visits. What I'm trying to do is find those customers who have visited within any given 30 day window and select those visits.

EX: The main focus is just going to be on three rows in the Table: ROW_ID, CUSTOMER_ID, VISIT_DATE (in the date format).

What I'm trying to get is when a customer has visited multiple times within a 30 day span. EX: CUSTOMER_ID #5 visits on the 10/8/2019 and again on 11/1/2019, I would want to see both rows

Answer 1

We could try using exists logic to handle the requirement:

SELECT ROW_ID, CUSTOMER_ID, VISIT_DATE
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2
              WHERE t2.CUSTOMER_ID = t2.CUSTOMER_ID AND
                    t2.ROW_ID <> t1.ROW_ID AND
                    ABS(t2.VISIT_DATE - t1.VISIT_DATE) <= 30);

The logic behind the above query reads cleanly as return any customer record where there another record for the same customer such that the two (different) records are within 30 days of each other.