From the below Table.

I want result as below

If we look the at the source table we can see that transactions at Sr. No 2,3,4 & 5 have occurred within 10 minutes, similarly transactions Sr. No 7,8 & 9 have occurred within 10 minutes so the total transaction for A will be 3 for C will be 2 and for B will be 2

Please let me know if your need any further clarification.

Will Appreciate your help, Thanks.

CodePudding user response：

This is a scaling problem but at reasonable table sizes and appropriate indexes this approach should work okay.

WITH t AS 
(
  SELECT s1 = t1.SrNo, u1 = t1.Username, 
         s2 = t2.SrNo, u2 = t2.Username
  FROM dbo.Transactions AS t1
  INNER JOIN dbo.Transactions AS t2
    ON t2.TransactionDateTime > t1.TransactionDateTime
   AND t2.TransactionDateTime <= DATEADD(MINUTE, 10, t1.TransactionDateTime)
)
SELECT Username = u1, TransactionCount = COUNT(DISTINCT s1)
  FROM 
  (
    SELECT u1, s1 FROM t UNION ALL SELECT u2, s2 FROM t
  ) AS u GROUP BY u1;

Output:

Example fiddle.