SQL Server 2019

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

select v.*
into tbl
from (values ('name1', convert(date, '7/7/2019  ')),
             ('name1', convert(date, '12/31/2019')),
             ('name1', convert(date, '1/23/2020 ')),
             ('name1', convert(date, '1/23/2020 ')),
             ('name1', convert(date, '2/12/2020 ')),
             ('name1', convert(date, '4/1/2020  ')),
             ('name2', convert(date, '6/30/2019 ')),
             ('name2', convert(date, '8/14/2019 ') )
     ) v(id, date)
8 rows affected

select * from tbl


id
date


name1
2019-07-07

name1
2019-12-31

name1
2020-01-23

name1
2020-01-23

name1
2020-02-12

name1
2020-04-01

name2
2019-06-30

name2
2019-08-14


with t as (
      select t.*, row_number() over (partition by id order by date) as seqnum
      from tbl t
     ),
     cte as (
      select t.id, t.date, dateadd(day, 90, t.date) as window_end, 1 as window, 1 as count, seqnum
      from t
      where seqnum = 1
      union all
      select t.id, t.date,
             (case when t.date > cte.window_end then dateadd(day, 90, t.date)
                   else cte.window_end
              end) as window_end,
             (case when t.date > cte.window_end then window + 1 else window end) as window,
             (case when t.date > cte.window_end then 1 else cte.count + 1 end) as count,
             t.seqnum
      from cte join
           t
           on t.id = cte.id and
              t.seqnum = cte.seqnum + 1
     )
select id, date, window, count
from cte
order by 1, 2


id
date
window
count


name1
2019-07-07
1
1

name1
2019-12-31
2
1

name1
2020-01-23
2
2

name1
2020-01-23
2
3

name1
2020-02-12
2
4

name1
2020-04-01
3
1

name2
2019-06-30
1
1

name2
2019-08-14
1
2

id	date
name1	2019-07-07
name1	2019-12-31
name1	2020-01-23
name1	2020-01-23
name1	2020-02-12
name1	2020-04-01
name2	2019-06-30
name2	2019-08-14