SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table MySQLTableWithActivityRecords (id int,Event int,UserIP varchar(15)
     ,UserName varchar(50) ,ActivityDate datetime);
insert  into MySQLTableWithActivityRecords values
 (1,0,'123.123.123.1','John.smith@domain.com','2024-01-27 15:01:01')
,(2,0,'123.123.123.1','Ian.Wally@domain.com','2024-01-27 15:02:02')
,(3,0,'123.123.123.1','Eve.Lotion@domain.com','2024-01-27 15:03:03')
,(4,0,'123.123.123.1','Tom.hardware@domain.com','2024-01-27 15:04:04')
,(5,0,'190.190.190.2','Meg.Meghan@domain.com','2024-01-27 15:05:05')
,(6,0,'190.190.190.2','New.Meghan@domain.com','2024-01-27 15:06:05')
,(7,0,'190.190.190.2','Meg.Meghan@domain.com','2024-01-27 15:07:05')
,(8,0,'190.190.190.3','New.User@domain.com','2024-01-27 15:16:05')
,(9,0,'190.190.190.3','New.User@domain.com','2024-01-27 15:17:05')
,(10,0,'190.190.190.4','User1@domain.com','2024-01-27 15:17:05')
,(11,0,'190.190.190.4','User2@domain.com','2024-01-27 16:17:05')
;
select * from MySQLTableWithActivityRecords;


id
Event
UserIP
UserName
ActivityDate


1
0
123.123.123.1
John.smith@domain.com
2024-01-27 15:01:01.000

2
0
123.123.123.1
Ian.Wally@domain.com
2024-01-27 15:02:02.000

3
0
123.123.123.1
Eve.Lotion@domain.com
2024-01-27 15:03:03.000

4
0
123.123.123.1
Tom.hardware@domain.com
2024-01-27 15:04:04.000

5
0
190.190.190.2
Meg.Meghan@domain.com
2024-01-27 15:05:05.000

6
0
190.190.190.2
New.Meghan@domain.com
2024-01-27 15:06:05.000

7
0
190.190.190.2
Meg.Meghan@domain.com
2024-01-27 15:07:05.000

8
0
190.190.190.3
New.User@domain.com
2024-01-27 15:16:05.000

9
0
190.190.190.3
New.User@domain.com
2024-01-27 15:17:05.000

10
0
190.190.190.4
User1@domain.com
2024-01-27 15:17:05.000

11
0
190.190.190.4
User2@domain.com
2024-01-27 16:17:05.000


SELECT [UserIP], COUNT([UserName]) qty
    FROM MySQLTableWithActivityRecords 
    WHERE Event = 0 AND  
    Format(ActivityDate, 'yyyy-MM-dd HH') = '2024-01-27 15'  
    GROUP BY [UserIP] 
    HAVING  COUNT([UserName]) > 1 
    ORDER BY [UserIP]


UserIP
qty


123.123.123.1
4

190.190.190.2
3

190.190.190.3
2


SELECT [UserIP], COUNT(DISTINCT [UserName]) qty
    FROM MySQLTableWithActivityRecords 
    WHERE Event = 0 AND  
    Format(ActivityDate, 'yyyy-MM-dd HH') = '2024-01-27 15'  
    GROUP BY [UserIP] 
    HAVING  COUNT(DISTINCT [UserName]) > 1 
    ORDER BY [UserIP]


UserIP
qty


123.123.123.1
4

190.190.190.2
2


select * 
from(
select *
  ,max(rnk)over(partition by UserIp,Format(ActivityDate, 'yyyy-MM-dd HH'))maxRnk
from(
select *
  ,rank()over(partition by UserIp,Format(ActivityDate, 'yyyy-MM-dd HH') 
               order by UserName) rnk
from MySQLTableWithActivityRecords
) q1
)q2
where maxRnk>1
order by activitydate


id
Event
UserIP
UserName
ActivityDate
rnk
maxRnk


1
0
123.123.123.1
John.smith@domain.com
2024-01-27 15:01:01.000
3
4

2
0
123.123.123.1
Ian.Wally@domain.com
2024-01-27 15:02:02.000
2
4

3
0
123.123.123.1
Eve.Lotion@domain.com
2024-01-27 15:03:03.000
1
4

4
0
123.123.123.1
Tom.hardware@domain.com
2024-01-27 15:04:04.000
4
4

5
0
190.190.190.2
Meg.Meghan@domain.com
2024-01-27 15:05:05.000
1
3

6
0
190.190.190.2
New.Meghan@domain.com
2024-01-27 15:06:05.000
3
3

7
0
190.190.190.2
Meg.Meghan@domain.com
2024-01-27 15:07:05.000
1
3

id	UserIP	UserName	ActivityDate
1	123.123.123.1	John.smith@domain.com	2024-01-27 15:01:01.000
2	123.123.123.1	Ian.Wally@domain.com	2024-01-27 15:02:02.000
3	123.123.123.1	Eve.Lotion@domain.com	2024-01-27 15:03:03.000
4	123.123.123.1	Tom.hardware@domain.com	2024-01-27 15:04:04.000
5	190.190.190.2	Meg.Meghan@domain.com	2024-01-27 15:05:05.000
6	190.190.190.2	New.Meghan@domain.com	2024-01-27 15:06:05.000
7	190.190.190.2	Meg.Meghan@domain.com	2024-01-27 15:07:05.000
8	190.190.190.3	New.User@domain.com	2024-01-27 15:16:05.000
9	190.190.190.3	New.User@domain.com	2024-01-27 15:17:05.000
10	190.190.190.4	User1@domain.com	2024-01-27 15:17:05.000
11	190.190.190.4	User2@domain.com	2024-01-27 16:17:05.000