MySQL how to write SQL to find excessive transactions in 15 minute windows? -

mysql
lets there credit card processing company. every time credit card used row gets inserted table.

create table tran(   id int,   tran_dt datetime,    card_id int,   merchant_id int,   amount int ); 

one wants know cards have been used 3+ times in 15 minute window @ same merchant.

my attempt:

select card_id, date(tran_dt), hour(tran_dt), merchant_id, count(*) tran group card_id, date(tran_dt), hour(tran_dt), merchant_id having count(*)>=3 

the first problem give excessive transactions per hour, not per 15 minute window. second problem not catch transactions cross hour mark ie @ 1:59pm , 2:01pm.

to make simpler, ok split hour 5 minute increments. not have check 1:00-1:15pm, 1:01-1:16pm, etc. ok check 1:00-1:15pm, 1:05-1:20pm, etc., if easier.

any ideas how fix sql? have feeling maybe need sql window functions, not yet available in mysql. or write stored procedure can @ each 15 block.

http://sqlfiddle.com/#!9/f2d74/1

you can convert date/time seconds , arithmetic on seconds value within 15 minute clock interval:

select card_id, min(date(tran_dt)) first_charge_time, merchant_id, count(*) tran group card_id, floor(to_seconds(tran_dt) / (60 * 15)), merchant_id having count(*) >= 3; 

the above uses to_seconds(). in earlier versions of mysql, can use unix_timestamp().

getting 15 minute interval more challenging. can express query as:

select t1.*, count(*) numtransactions tran t1 join      tran t2      on t1.merchant_id = t2.merchanti_d ,         t1.card_id = t2.card_id ,         t2.tran_dt >= t1.tran_dt ,         t2.tran_dt < t1.tran_dt + interval 15 minute  group t1.id having numtransactions >= 3; 

performance of query might problematic. index on trans(card_id, merchant_id, tran_dt) should lot.