Q:

SQL Server : find store that has 4 or more transactions within 10 minutes

I want to find a list of the stores that have 4 or more transactions within 10 minutes interval
Sample data in SQL Server 2012:
IF OBJECT_ID('tempdb..#t') is not null DROP TABLE #t;
create TABLE #t
(
 cID int,  trans_time  datetime,  store_address varchar(20)
)
insert into #t values
 (111, '5/27/18 6:36 AM', '79 street, NY')
,(112, '5/27/18 6:53 AM', '79 street, NY')
,(113, '5/27/18 6:54 AM', '79 street, NY')
,(114, '5/27/18 6:55 AM', '79 street, NY')
,(115, '5/27/18 6:59 AM', '79 street, NY')
,(116, '5/27/18 9:45 PM', '79 street, NY')
,(117, '3/24/18 6:35 AM', '44 tree ave,FL')
,(118, '3/24/18 6:36 AM', '44 tree ave,FL')
,(119, '3/24/18 6:36 AM', '44 tree ave,FL')
,(120, '3/24/18 6:36 AM', '2 pop ave, NJ')

Expected output
cID   transa_time       store_address
-------------------------------------
112   5/27/18 6:53 AM   79 street, NY
113   5/27/18 6:54 AM   79 street, NY
114   5/27/18 6:55 AM   79 street, NY
115   5/27/18 6:59 AM   79 street, NY

my solution not working properly
with CTE as (select #t.*, isnull( lead(trans_time, 3) over (partition by store_address order by trans_time),trans_time) as t3 from #t)
select CTE.*
from CTE
where DATEDIFF(minute, trans_time,t3) <= 10
order by cID

A:

If your SQL Server doesn't support LEAD/LAG, I think this should work. If it does support it the other answer is better.
edit: Fixed case when multiple transactions occur in the same minute. It assumes/hopes that cID is a transaction ID.
CREATE TABLE TEST_DATA 
  ( transaction_id INTEGER,
    store_id INTEGER,
    transaction_ts DATETIME
  );

INSERT INTO TEST_DATA VALUES (1, 1,'2018-07-09 8:00:00');
INSERT INTO TEST_DATA VALUES (2, 1,'2018-07-09 8:00:00');
INSERT INTO TEST_DATA VALUES (3, 1,'2018-07-09 8:01:00');
INSERT INTO TEST_DATA VALUES (4, 1,'2018-07-09 8:02:00');
INSERT INTO TEST_DATA VALUES (5, 1,'2018-07-09 8:03:00');
INSERT INTO TEST_DATA VALUES (6, 1,'2018-07-09 8:04:00');
INSERT INTO TEST_DATA VALUES (7, 2,'2018-07-09 8:00:00');
INSERT INTO TEST_DATA VALUES (8, 2,'2018-07-09 8:01:00');
INSERT INTO TEST_DATA VALUES (9, 2,'2018-07-09 8:02:00');
INSERT INTO TEST_DATA VALUES (10, 2,'2018-07-09 8:15:00');

SELECT store_id
  FROM (SELECT DISTINCT 
               TD1.transaction_id,
               TD1.store_id,
               TD1.transaction_ts
          FROM TEST_DATA TD1
         INNER
          JOIN TEST_DATA TD2
            ON TD1.store_id = TD2.store_id
           AND DateDiff(minute,TD1.transaction_ts, TD2.transaction_ts) >= -10
           AND TD1.transaction_id <> TD2.transaction_id
       ) TMP
 GROUP
    BY store_id
HAVING COUNT(1) >= 4;

A:

By our understanding,
 ;with date as      --date cte for 10min interval transcation rows
 (
  select distinct d.cid,d.transa_time,d.store_address
  from #4date d
  join (
   select distinct d1.cid 'cid1',d2.cid 'cid2'
   , d1.transa_time 't1',d2.transa_time 't2'
   from #4date d1
   join #4date d2   --self join
   on d1.store_address = d2.store_address
   and DATEDIFF(MI,d1.transa_time,d2.transa_time) < 10
   and d1.cid <> d2.cid
   and d1.cid < d2.cid
  )dd
  on d.cID=dd.cid1 or d.cID=dd.cid2     
 ),
 address as         --address for min count 4 of store address
 (
 select store_address from #4date 
 group by store_address having COUNT(store_address) > 3
 )
 select * from date d
 join address a
 on d.store_address = a.store_address

Input table
create TABLE #4date 
(
 cID int,  transa_time  datetime,     store_address varchar(20)
)

insert into #4date values
 (111, '5/27/18 6:36 AM', '79 street, NY')
,(112, '5/27/18 6:53 AM', '79 street, NY')
,(113, '5/27/18 6:53 AM', '79 street, NY')
,(114, '5/27/18 6:53 AM', '79 street, NY')
,(115, '5/27/18 6:59 AM', '79 street, NY')  --here I change AM
,(116, '5/27/18 9:45 PM', '79 street, NY')
,(117, '3/24/18 6:35 AM', '44 tree ave,FL')
,(118, '3/24/18 6:36 AM', '44 tree ave,FL')
,(119, '3/24/18 6:36 AM', '44 tree ave,FL')
,(120, '3/24/18 6:36 AM', '2 pop ave, NJ')

Let me know what you got!

