Postgresql filtering by value over rolling time period

postgresqlquerytimestamp

I am newbie to postgresql and still getting my head around. I have used windows function etc. till date and yes I can make some sense.

I have got the following data set:

enter image description here

I need to find those customers whose amount > 2 for 4 minutes in a rolling fashion, considering the data for a day. By that I mean in the given data set, customer 1 is one such as it has got amount > 2 at 16:01,16:02,16:03,16:04 and then at 16:02,16:03,16:04, 16:05. If there is any such single occurrence, the customer needs to be selected. Customer 2 doesn't have so. Wondering what's the best approach to do this?

Any help will be much appreciated.

Regards,

Best Answer

Schematically:

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL 4 minute
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)

or

SELECT DISTINCT t1.customer
FROM test t1
WHERE NOT EXISTS (SELECT 1
                  FROM test t2
                  WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL 4 minute
                    AND t1.customer = t2.customer
                    AND t2.amount <= 2)

If time roll is to be "full", use 1st query adding relevant restriction:

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND COUNT(t2.amount) = 5

or

SELECT DISTINCT t1.customer
FROM test t1, test t2
WHERE t2.purshase_ts BETWEEN t1.purshase_ts AND t1.purshase_ts + INTERVAL '4 minute'
  AND t1.customer = t2.customer
GROUP BY t1.customer, t1.purshase_ts
HAVING SUM(CASE WHEN t2.amount > 2 THEN 1 ELSE 0 END) = COUNT(t2.amount)
   AND MAX(t2.purshase_ts) - MIN(t2.purshase_ts) = INTERVAL '4 MINUTE'

fiddle

Related Question