SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table data (playid int,	userid varchar(10),	created	datetime,stopped datetime);
insert into data values(1,'a01','01/30/2021 02:29:58.560','01/30/2021 02:40:56.558');
insert into data values(2,'a01','01/30/2021 02:41:24.023','01/30/2021 02:55:24.113');
insert into data values(3,'a01','01/30/2021 02:57:30.179','01/30/2021 03:11:14.867');
insert into data values(4,'a01','01/30/2021 03:11:41.098','01/30/2021 03:22:50.156');
insert into data values(5,'a01','01/30/2021 03:23:20.545','01/30/2021 03:36:37.027');
insert into data values(6,'a01','01/30/2021 03:46:10.238','01/30/2021 03:59:17.526');
insert into data values(7,'a01','01/30/2021 03:59:57.020','01/30/2021 04:16:25.948');
insert into data values(8,'a01','01/30/2021 04:26:28.969','01/30/2021 04:30:11.863');
insert into data values(9,'a01','01/30/2021 04:30:14.438','01/30/2021 04:41:42.232');
insert into data values(10,'a01','01/30/2021 04:43:17.670','01/30/2021 04:45:10.443');
insert into data values(11,'a01','01/30/2021 04:45:14.503','01/30/2021 04:47:07.082');
insert into data values(12,'a01','01/30/2021 04:47:10.335','01/30/2021 04:49:30.960');
insert into data values(13,'a01','01/30/2021 04:49:34.146','01/30/2021 04:50:54.460');
insert into data values(14,'a01','01/30/2021 04:50:57.948','01/30/2021 05:11:01.246');
insert into data values(15,'a01','01/30/2021 05:39:29.387','01/30/2021 05:41:39.509');
insert into data values(16,'a01','01/30/2021 05:41:44.525','01/30/2021 05:43:38.231');
insert into data values(17,'a01','01/30/2021 05:43:40.786','01/30/2021 05:54:40.711');
insert into data values(18,'a01','01/30/2021 05:55:10.852','01/30/2021 05:58:24.262');
insert into data values(19,'a01','01/30/2021 05:58:29.438','01/30/2021 06:00:50.871');
insert into data values(20,'a01','01/30/2021 06:00:54.169','01/30/2021 06:12:37.210');
insert into data values(21,'a01','01/30/2021 14:41:01.530','01/30/2021 14:53:01.349');
insert into data values(22,'a01','01/30/2021 14:53:48.053','01/30/2021 15:05:09.533');
insert into data values(23,'a01','01/31/2021 15:11:08.547','01/31/2021 15:11:37.565');
insert into data values(24,'a01','01/31/2021 15:11:40.248','01/31/2021 15:13:40.218');
insert into data values(25,'a01','01/31/2021 15:13:59.373','01/31/2021 15:31:54.100');
insert into data values(26,'a01','01/31/2021 15:32:23.204','01/31/2021 15:46:33.994');
insert into data values(27,'a01','01/31/2021 16:55:19.141','01/31/2021 17:03:43.464');
27 rows affected

          select d.playid,d.userid,d.created,d.stopped,dt.minstopped from data d
	  outer apply (
	  select max(stopped) minstopped from data dt 
	  where  d.playid<dt.playid and datediff(minute,d.created,dt.stopped)<=120
	  and d.userid=dt.userid)dt 


playid
userid
created
stopped
minstopped


1
a01
2021-01-30 02:29:58.560
2021-01-30 02:40:56.557
2021-01-30 04:16:25.947

2
a01
2021-01-30 02:41:24.023
2021-01-30 02:55:24.113
2021-01-30 04:41:42.233

3
a01
2021-01-30 02:57:30.180
2021-01-30 03:11:14.867
2021-01-30 04:50:54.460

4
a01
2021-01-30 03:11:41.097
2021-01-30 03:22:50.157
2021-01-30 05:11:01.247

5
a01
2021-01-30 03:23:20.547
2021-01-30 03:36:37.027
2021-01-30 05:11:01.247

6
a01
2021-01-30 03:46:10.237
2021-01-30 03:59:17.527
2021-01-30 05:43:38.230

7
a01
2021-01-30 03:59:57.020
2021-01-30 04:16:25.947
2021-01-30 05:58:24.263

8
a01
2021-01-30 04:26:28.970
2021-01-30 04:30:11.863
2021-01-30 06:12:37.210

9
a01
2021-01-30 04:30:14.437
2021-01-30 04:41:42.233
2021-01-30 06:12:37.210

10
a01
2021-01-30 04:43:17.670
2021-01-30 04:45:10.443
2021-01-30 06:12:37.210

11
a01
2021-01-30 04:45:14.503
2021-01-30 04:47:07.083
2021-01-30 06:12:37.210

12
a01
2021-01-30 04:47:10.337
2021-01-30 04:49:30.960
2021-01-30 06:12:37.210

13
a01
2021-01-30 04:49:34.147
2021-01-30 04:50:54.460
2021-01-30 06:12:37.210

14
a01
2021-01-30 04:50:57.947
2021-01-30 05:11:01.247
2021-01-30 06:12:37.210

15
a01
2021-01-30 05:39:29.387
2021-01-30 05:41:39.510
2021-01-30 06:12:37.210

16
a01
2021-01-30 05:41:44.527
2021-01-30 05:43:38.230
2021-01-30 06:12:37.210

17
a01
2021-01-30 05:43:40.787
2021-01-30 05:54:40.710
2021-01-30 06:12:37.210

18
a01
2021-01-30 05:55:10.853
2021-01-30 05:58:24.263
2021-01-30 06:12:37.210

19
a01
2021-01-30 05:58:29.437
2021-01-30 06:00:50.870
2021-01-30 06:12:37.210

20
a01
2021-01-30 06:00:54.170
2021-01-30 06:12:37.210
null

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null


with cte as
   (
	  select d.playid,d.userid,d.created,d.stopped,dt.minstopped from data d
	  outer apply (
	  select max(stopped) minstopped from data dt 
	  where  d.playid<dt.playid and datediff(minute,d.created,dt.stopped)<=120
	  and d.userid=dt.userid)dt 
   ),
   cte2 as 
   (
	select top 1 *, 1 level from cte order by playid 
	union all
	select cte.* ,level+2 level from cte inner join cte2 on cte.stopped>cte2.minstopped
   )
   select * from cte2
	


playid
userid
created
stopped
minstopped
level


1
a01
2021-01-30 02:29:58.560
2021-01-30 02:40:56.557
2021-01-30 04:16:25.947
1

8
a01
2021-01-30 04:26:28.970
2021-01-30 04:30:11.863
2021-01-30 06:12:37.210
3

9
a01
2021-01-30 04:30:14.437
2021-01-30 04:41:42.233
2021-01-30 06:12:37.210
3

10
a01
2021-01-30 04:43:17.670
2021-01-30 04:45:10.443
2021-01-30 06:12:37.210
3

11
a01
2021-01-30 04:45:14.503
2021-01-30 04:47:07.083
2021-01-30 06:12:37.210
3

12
a01
2021-01-30 04:47:10.337
2021-01-30 04:49:30.960
2021-01-30 06:12:37.210
3

13
a01
2021-01-30 04:49:34.147
2021-01-30 04:50:54.460
2021-01-30 06:12:37.210
3

14
a01
2021-01-30 04:50:57.947
2021-01-30 05:11:01.247
2021-01-30 06:12:37.210
3

15
a01
2021-01-30 05:39:29.387
2021-01-30 05:41:39.510
2021-01-30 06:12:37.210
3

16
a01
2021-01-30 05:41:44.527
2021-01-30 05:43:38.230
2021-01-30 06:12:37.210
3

17
a01
2021-01-30 05:43:40.787
2021-01-30 05:54:40.710
2021-01-30 06:12:37.210
3

18
a01
2021-01-30 05:55:10.853
2021-01-30 05:58:24.263
2021-01-30 06:12:37.210
3

19
a01
2021-01-30 05:58:29.437
2021-01-30 06:00:50.870
2021-01-30 06:12:37.210
3

20
a01
2021-01-30 06:00:54.170
2021-01-30 06:12:37.210
null
3

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
3

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
3

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
3

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
3

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
3

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
3

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
3

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7

21
a01
2021-01-30 14:41:01.530
2021-01-30 14:53:01.350
2021-01-30 15:05:09.533
5

22
a01
2021-01-30 14:53:48.053
2021-01-30 15:05:09.533
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
5

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
5

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
5

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
5

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
5

23
a01
2021-01-31 15:11:08.547
2021-01-31 15:11:37.567
2021-01-31 17:03:43.463
7

24
a01
2021-01-31 15:11:40.247
2021-01-31 15:13:40.217
2021-01-31 17:03:43.463
7

25
a01
2021-01-31 15:13:59.373
2021-01-31 15:31:54.100
2021-01-31 17:03:43.463
7

26
a01
2021-01-31 15:32:23.203
2021-01-31 15:46:33.993
2021-01-31 17:03:43.463
7

27
a01
2021-01-31 16:55:19.140
2021-01-31 17:03:43.463
null
7


 with cte as
   (
	  select d.playid,d.userid,d.created,d.stopped,dt.minstopped from data d
	  outer apply (
	  select max(stopped) minstopped from data dt 
	  where  d.playid<dt.playid and datediff(minute,d.created,dt.stopped)<=120
	  and d.userid=dt.userid)dt 
   ),
   cte2 as 
   (
	select top 1 *, 1 level from cte order by playid 
	union all
	select cte.* ,level+2 level from cte inner join cte2 on cte.stopped>cte2.minstopped
   ),
   cte3 as 
   (
	select *,row_number()over(partition by level order by playid) rn from cte2
   )
   SELECT playid,userid,created,minstopped stopped FROM CTE3 WHERE RN=1


playid
userid
created
stopped


1
a01
2021-01-30 02:29:58.560
2021-01-30 04:16:25.947

8
a01
2021-01-30 04:26:28.970
2021-01-30 06:12:37.210

21
a01
2021-01-30 14:41:01.530
2021-01-30 15:05:09.533

23
a01
2021-01-31 15:11:08.547
2021-01-31 17:03:43.463
playid	userid	created	stopped	minstopped
1	a01	2021-01-30 02:29:58.560	2021-01-30 02:40:56.557	2021-01-30 04:16:25.947
2	a01	2021-01-30 02:41:24.023	2021-01-30 02:55:24.113	2021-01-30 04:41:42.233
3	a01	2021-01-30 02:57:30.180	2021-01-30 03:11:14.867	2021-01-30 04:50:54.460
4	a01	2021-01-30 03:11:41.097	2021-01-30 03:22:50.157	2021-01-30 05:11:01.247
5	a01	2021-01-30 03:23:20.547	2021-01-30 03:36:37.027	2021-01-30 05:11:01.247
6	a01	2021-01-30 03:46:10.237	2021-01-30 03:59:17.527	2021-01-30 05:43:38.230
7	a01	2021-01-30 03:59:57.020	2021-01-30 04:16:25.947	2021-01-30 05:58:24.263
8	a01	2021-01-30 04:26:28.970	2021-01-30 04:30:11.863	2021-01-30 06:12:37.210
9	a01	2021-01-30 04:30:14.437	2021-01-30 04:41:42.233	2021-01-30 06:12:37.210
10	a01	2021-01-30 04:43:17.670	2021-01-30 04:45:10.443	2021-01-30 06:12:37.210
11	a01	2021-01-30 04:45:14.503	2021-01-30 04:47:07.083	2021-01-30 06:12:37.210
12	a01	2021-01-30 04:47:10.337	2021-01-30 04:49:30.960	2021-01-30 06:12:37.210
13	a01	2021-01-30 04:49:34.147	2021-01-30 04:50:54.460	2021-01-30 06:12:37.210
14	a01	2021-01-30 04:50:57.947	2021-01-30 05:11:01.247	2021-01-30 06:12:37.210
15	a01	2021-01-30 05:39:29.387	2021-01-30 05:41:39.510	2021-01-30 06:12:37.210
16	a01	2021-01-30 05:41:44.527	2021-01-30 05:43:38.230	2021-01-30 06:12:37.210
17	a01	2021-01-30 05:43:40.787	2021-01-30 05:54:40.710	2021-01-30 06:12:37.210
18	a01	2021-01-30 05:55:10.853	2021-01-30 05:58:24.263	2021-01-30 06:12:37.210
19	a01	2021-01-30 05:58:29.437	2021-01-30 06:00:50.870	2021-01-30 06:12:37.210
20	a01	2021-01-30 06:00:54.170	2021-01-30 06:12:37.210	null
21	a01	2021-01-30 14:41:01.530	2021-01-30 14:53:01.350	2021-01-30 15:05:09.533
22	a01	2021-01-30 14:53:48.053	2021-01-30 15:05:09.533	null
23	a01	2021-01-31 15:11:08.547	2021-01-31 15:11:37.567	2021-01-31 17:03:43.463
24	a01	2021-01-31 15:11:40.247	2021-01-31 15:13:40.217	2021-01-31 17:03:43.463
25	a01	2021-01-31 15:13:59.373	2021-01-31 15:31:54.100	2021-01-31 17:03:43.463
26	a01	2021-01-31 15:32:23.203	2021-01-31 15:46:33.993	2021-01-31 17:03:43.463
27	a01	2021-01-31 16:55:19.140	2021-01-31 17:03:43.463	null