SQL Server 2019

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

create table mytable (name nvarchar(50), createdDate datetime)
insert into mytable
    values ('John', '20160101 20:36:12'), ('Max', '20160101 20:36:12'), ('Max', '20160101 20:37:12'), ('Max', '20160101 20:38:12'), ('John', '20160101 20:38:12'), ('John', '20160101 20:39:12'), ('Max', '20160101 20:41:12')
7 rows affected

select * from mytable


name
createdDate


John
2016-01-01 20:36:12.000

Max
2016-01-01 20:36:12.000

Max
2016-01-01 20:37:12.000

Max
2016-01-01 20:38:12.000

John
2016-01-01 20:38:12.000

John
2016-01-01 20:39:12.000

Max
2016-01-01 20:41:12.000


select name, count(*), min(createddate), max(createddate)
from (select t.*,
             row_number() over (order by createddate) as seqnum,
             row_number() over (partition by name order by createddate) as seqnum_2
      from mytable t
     ) t
group by name, (seqnum - seqnum_2)
having count(*) >= 2
order by min(createddate)


name
(No column name)
(No column name)
(No column name)


Max
3
2016-01-01 20:36:12.000
2016-01-01 20:38:12.000

John
2
2016-01-01 20:38:12.000
2016-01-01 20:39:12.000

name	createdDate
John	2016-01-01 20:36:12.000
Max	2016-01-01 20:36:12.000
Max	2016-01-01 20:37:12.000
Max	2016-01-01 20:38:12.000
John	2016-01-01 20:38:12.000
John	2016-01-01 20:39:12.000
Max	2016-01-01 20:41:12.000