Select all customers that logged in at least three times within a time interval

Question

I have a login table that contains the ID of the customer and the timestamp of the login time (customerid, timestamp).

I am looking to get all the customer IDs that logged in at least three times within sixty minutes. By the way, the login table is huge. Self joining is not an option.

For example:

customer id | timestamp
1           | 2016-08-16 00:00
2           | 2016-08-16 00:00
3           | 2016-08-16 00:00
1           | 2016-08-16 00:25
2           | 2016-08-16 01:25
3           | 2016-08-16 00:25
1           | 2016-08-16 00:47
2           | 2016-08-16 01:27
3           | 2016-08-16 02:25
3           | 2016-08-16 03:25
1           | 2016-08-16 01:05

For this example, the query should return only customerid 1. Any ideas?

Answer 1

Tested with rexTester: http://rextester.com/RMST24716 (thanks TT.!)

 CREATE TABLE loginTable (id INT NOT NULL, timestamp DATETIME NOT NULL);

 INSERT INTO loginTable (id, timestamp) values 
( 1, '2016-08-16 00:00'),
( 2, '2016-08-16 00:00'),
( 3, '2016-08-16 00:00'),
( 1, '2016-08-16 00:25'),
( 2, '2016-08-16 01:25'),
( 3, '2016-08-16 00:25'),
( 1, '2016-08-16 00:47'),
( 2, '2016-08-16 01:27'),
( 3, '2016-08-16 02:25'),
( 3, '2016-08-16 03:25'),
( 1, '2016-08-16 01:05');


SELECT distinct a.id
FROM loginTable as a 
join loginTable as b on a.id = b.id and a.timestamp < b.timestamp
join loginTable as c on b.id = c.id and b.timestamp < c.timestamp
where Datediff(minute, a.timestamp, c.timestamp) <= 60;