Find duplicate data in last 1 hour

I am looking for a SQL script to find the data which has more than 2 entries in last 1 hour. I have a table having user_id & event_time. I want a way to find out if the user_id has more than 1 entries in last 1 hour.

I have tried below till now:

1. Create temp table to put all duplicate entries :

SELECT a.*
INTO #temp 
FROM Table a
JOIN (
    SELECT USERID, COUNT(*) AS Duplicates
    FROM Table
    GROUP BY userid
    HAVING count(*) > 1
) AS b ON a.userid = b.USERID

2. Run self Joins to fetch records having time difference of 1 hour or less:

SELECT a.*
FROM #temp a
INNER JOIN #temp b ON a.userid = b.USERID
WHERE DATEDIFF(hour, a.EVENTTIME, b.EVENTTIME) = 1

Once first script is ran it gives around 800+ rows for duplicate data. But after running the second script the data I get is in thousands. Can anyone help here?

Answer

cross apply can be used to get all related events for each event according to your criteria as follows:

With CTE As (
Select USERID, EVENTTIME, Row_Number() Over (Order by USERID, EVENTTIME) As ID
From Tbl
)
Select a.ID, a.USERID, a.EVENTTIME, T.ID, T.USERID, T.EVENTTIME
From CTE As a Cross Apply (Select ID, USERID, EVENTTIME 
                           From CTE 
                           Where Abs(datediff(minute, a.EVENTTIME, EVENTTIME))<=60
                                 And USERID=a.USERID And ID<>a.ID) As T
Order by a.ID, a.USERID, a.EVENTTIME, T.ID, T.USERID, T.EVENTTIME

or you can get a list of events without binding to a specific event:

With CTE As (
Select USERID, EVENTTIME, Row_Number() Over (Order by USERID, EVENTTIME) As ID
From Tbl
)
Select T.USERID, T.EVENTTIME
From CTE As a Cross Apply (Select USERID, EVENTTIME 
                           From CTE 
                           Where Abs(datediff(minute, a.EVENTTIME, EVENTTIME))<=60
                                 And USERID=a.USERID And ID<>a.ID) As T
Group by T.USERID, T.EVENTTIME

db<>fiddle

to get the events only for last hour, you can add the appropriate filter to Where clause in CTE.

With CTE As (
Select USERID, EVENTTIME, Row_Number() Over (Order by USERID, EVENTTIME) As ID
From Tbl
Where EVENTTIME Between dateadd(minute, -60, GetDate()) And GetDate()
)
Select T.USERID, T.EVENTTIME
From CTE As a Cross Apply (Select USERID, EVENTTIME 
                           From CTE 
                           Where Abs(datediff(minute, a.EVENTTIME, EVENTTIME))<=60
                                 And USERID=a.USERID 
                                 And ID<>a.ID) As T
Group by T.USERID, T.EVENTTIME