Looking for duplicate transactions within a 5 minutes over a 24 hour time period

Question

I am looking for duplicate transactions between a 5 minute window during a 24 hour period. I am trying to find users abusing other users access. Here is what I have so far, but it is only searching the past 5 minutes and not searching the 24 hour period. It is ORACLE.

SELECT p.id, Count(*) count
FROM tranledg tl,
     patron p
WHERE p.id = tl.patronid
  AND tl.trandate > (sysdate-5/1440)
  AND tl.plandesignation in ('1')
  AND p.id in (select id from tranledg tl where tl.trandate > (sysdate-1))
GROUP BY p.id
HAVING COUNT(*)> 1

Example data:

Patron

id  |  Name      
--------------------------
1   | Joe
2   | Henry
3   | Tom
4   | Mary
5   | Sue
6   | Marie

Tranledg

tranid  |  trandate              |   location    |  patronid     
--------------------------
1       |  2015-03-01 12:01:00   |   1500        |  1
2       |  2015-03-01 12:01:15   |   1500        |  2
3       |  2015-03-01 12:03:30   |   1500        |  1
4       |  2015-03-01 12:04:00   |   1500        |  3
5       |  2015-03-01 15:01:00   |   1500        |  4
6       |  2015-03-01 15:01:15   |   1500        |  4
7       |  2015-03-01 17:01:15   |   1500        |  2
8       |  2015-03-01 18:01:30   |   1500        |  1
9       |  2015-03-01 19:02:00   |   1500        |  3
10      |  2015-03-01 20:01:00   |   1500        |  4
11      |  2015-03-01 21:01:00   |   1500        |  5

I would expect the following data to return:

ID  |   COUNT
1   |    2
4   |    2

Answer 1

You can use an analytic clause with a range window like this:

select *
  from (select tranid
             , patronid
             , count(*) over(partition by patronid
                             order by trandate
                             range between       0 preceding
                                       and 5/60/24 following) count
          from tranledg
         where trandate >= sysdate-1)
 where count > 1

It will output all transactions that are followed with more ones for the same patronid in the range of 5 minutes along with the count of the transactions in the range (you did not specify what to do if there are more than one such a range or when the ranges are overlapping).

Output on the test data (without the condition for sysdate as it already passed):

TRANID PATRONID COUNT
------ -------- -----
     1        1     2
     5        4     2

Answer 2

I did it using Postgres online, Oracle version very similar, only be carefull with date operation.

SQL DEMO

You need a self join.

SELECT T1.patronid, count(*)
FROM Tranledg T1
JOIN Tranledg T2
  ON T2."trandate" BETWEEN T1."trandate" + '-2 minute' AND T1."trandate" + '2 minute'
 AND T1."patronid" = T2."patronid"
 AND T1."tranid" <> T2."tranid"
GROUP BY T1.patronid;

OUTPUT

You need to fix the data, so 1 has two records.