SQL Server 2017

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

select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) 
 Jun 12 2020 20:39:00 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table t(timestamp1 datetime, category varchar(10));
insert into t values('2018-10-01 04:06:12','Cat1')
insert into t values('2018-10-01 05:07:18','Cat1')
insert into t values('2018-10-01 05:07:19','Cat1')
insert into t values('2018-10-01 05:07:20','Cat1')
insert into t values('2018-10-01 06:09:29','Cat1')
insert into t values('2018-10-01 05:15:20','Cat1')
insert into t values('2018-10-01 07:24:12','Cat2')
insert into t values('2018-10-01 07:30:43','Cat2')
insert into t values('2018-10-01 07:59:13','Cat2')
insert into t values('2018-10-01 08:02:15','Cat2')
insert into t values('2018-10-01 10:09:25','Cat2')
insert into t values('2018-10-01 11:13:42','Cat2')
12 rows affected

with raw_data
  as(select timestamp1
            ,category
            ,case when datediff(mi,lag(timestamp1) over(partition by category order by timestamp1),timestamp1) >5 
                    or lag(timestamp1) over(partition by category order by timestamp1) is null
                  then row_number() over(partition by category order by timestamp1)                  
              end as grps_of_5
       from t  
     )
   ,curated_data
      as (select 
                 x.*
                 ,max(grps_of_5) over(partition by category order by timestamp1) as max_val
            from raw_data x
           )
 select * from curated_data


timestamp1
category
grps_of_5
max_val


2018-10-01 04:06:12.000
Cat1
1
1

2018-10-01 05:07:18.000
Cat1
2
2

2018-10-01 05:07:19.000
Cat1
null
2

2018-10-01 05:07:20.000
Cat1
null
2

2018-10-01 05:15:20.000
Cat1
5
5

2018-10-01 06:09:29.000
Cat1
6
6

2018-10-01 07:24:12.000
Cat2
1
1

2018-10-01 07:30:43.000
Cat2
2
2

2018-10-01 07:59:13.000
Cat2
3
3

2018-10-01 08:02:15.000
Cat2
null
3

2018-10-01 10:09:25.000
Cat2
5
5

2018-10-01 11:13:42.000
Cat2
6
6


Warning: Null value is eliminated by an aggregate or other SET operation.


with raw_data
  as(select timestamp1
            ,category
            ,case when datediff(mi,lag(timestamp1) over(partition by category order by timestamp1),timestamp1) >5 
                    or lag(timestamp1) over(partition by category order by timestamp1) is null
                  then row_number() over(partition by category order by timestamp1)                  
              end as grps_of_5
       from t  
     )
   ,curated_data
      as (select max(grps_of_5) over(partition by category order by timestamp1) as max_val
                 ,x.*
            from raw_data x
           )
 select category,count(distinct max_val) as cnt
   from curated_data
group by category 


category
cnt


Cat1
4

Cat2
5


Warning: Null value is eliminated by an aggregate or other SET operation.

timestamp1	category	grps_of_5	max_val
2018-10-01 04:06:12.000	Cat1	1	1
2018-10-01 05:07:18.000	Cat1	2	2
2018-10-01 05:07:19.000	Cat1	null	2
2018-10-01 05:07:20.000	Cat1	null	2
2018-10-01 05:15:20.000	Cat1	5	5
2018-10-01 06:09:29.000	Cat1	6	6
2018-10-01 07:24:12.000	Cat2	1	1
2018-10-01 07:30:43.000	Cat2	2	2
2018-10-01 07:59:13.000	Cat2	3	3
2018-10-01 08:02:15.000	Cat2	null	3
2018-10-01 10:09:25.000	Cat2	5	5
2018-10-01 11:13:42.000	Cat2	6	6