How to prevent SQL query from returning overlapping groups?

Question

I'm trying to generate a report that displays the number of failed login attempts that happen within 30 minutes of each other. The data for this report is in a SQL database.

This is the query I'm using to pull the data out.

SELECT 
  A.LoginID, 
  A.LogDatetime AS firstAttempt,
  MAX(B.LogDatetime) AS lastAttempt,
  COUNT(B.LoginID) + 1 AS attempts 
FROM 
  UserLoginHistory A
  JOIN UserLoginHistory B ON A.LoginID = B.LoginID
WHERE 
  A.SuccessfulFlag = 0 
  AND B.SuccessfulFlag = 0
  AND A.LogDatetime < B.LogDatetime 
  AND B.LogDatetime <= DATEADD(minute, 30, A.LogDatetime)
GROUP BY
  A.LoginID, A.LogDatetime
ORDER BY
  A.LoginID, A.LogDatetime

This returns results that looks something like this:

Row	LoginID	firstAttempt	lastAttempt	attempts
1	1	2022-05-01 00:00	2022-05-01 00:29	6
2	1	2022-05-01 00:06	2022-05-01 00:33	6
3	1	2022-05-01 00:13	2022-05-01 00:39	6
4	1	2022-05-01 00:15	2022-05-01 00:45	6
5	1	2022-05-01 00:20	2022-05-01 00:50	6
6	1	2022-05-01 00:29	2022-05-01 00:55	6
7	1	2022-05-01 00:33	2022-05-01 01:01	6
8	1	2022-05-01 00:39	2022-05-01 01:04	6
...	...	...	...	...

However, you can see that the rows overlap a lot. For example, row 1 shows attempts from 00:00 to 00:29, which overlaps with row 2 showing attempts from 00:06 to 00:33. Row 2 ought to be like row 7 (00:33 - 01:01), since that row's firstAttempt is the next one after row 1's lastAttempt.

Answer 1

You might need to use recursive CTE's or insert your data into a temp table and loop it with updates to remove the overlaps.

Do you need to have set starting times? As a quick work around you could round down the the DATETIME to 30 minute intervals, that would ensure the logins don't overlap but it will only group the attempts by 30 minute buckets

SELECT 
  A.LoginID, 
  DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, '2022-01-01', A.LogDatetime) / 30.0, 0) * 30, '2022-01-01') AS LoginInterval,
  MIN(A.LogDatetime) AS firstAttempt,
  MAX(A.LogDatetime) AS lastAttempt,
  COUNT(*) attempts
FROM 
  UserLoginHistory A
WHERE 
  A.SuccessfulFlag = 0 
GROUP BY
  A.LoginID, DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, '2022-01-01', A.LogDatetime) / 30.0, 0) * 30, '2022-01-01')
ORDER BY
  A.LoginID, LoginInterval