SQL Dynamic Start-time Window Grouping

I am looking to write a SQL Query to return the 'window_group' column below given the 'dates' column:

This window_group column is generated such that..

Each window ends window_length days after the first date within a window
New windows begin upon the first date after the last window ends

| date      | window_group |
|-----------|--------------|
| 1/1/2020  |     a        |
| 1/2/2020  |     a        |
| 1/5/2020  |     b        |
| 1/7/2020  |     b        |
| 1/12/2020 |     c        |
| 1/13/2020 |     c        |

The expected sql output is above, using a window-length of 3.

This graphic may also help you to understand:

1  2        5     7             12 13 
|  |        |     |              |  |     
+--+--+--+--+--+--+--+--+--+--+--+--+--+--...
1  2  3  4  5  6  7  8  9  10 11 12 13 14
a  a  a  .  b  b  b  .  .  .  .  c  c  c


window_length: 3

groupings:
a: [1, 2]
b: [5, 7]
c: [12, 13]

I have tried to implement this using a window function and lag, to determine the day of the first row in the previous record's assigned group, but this does not work, as I cannot access the updated assigned group while we are assigning it.

Any help would be very much appreciated!

Solution

This is tricky logic. You need a recursive CTE for this:

with t as (
      select t.*, row_number() over (order by date) as seqnum
      from <table> t
     ),
     cte as (
      select t.date, dateadd(day, 3, t.date) as grpend, 1 as grp, t.seqnum
      from t
      where seqnum = 1
      union all
      select t.date,
             (case when t.date <= cte.grpend then cte.grpend else dateadd(day, 3, t.date) end),
             (case when t.date <= cte.grpend then cte.grp else cte.grp + 1 end),
             t.seqnum
      from cte join
           t
           on t.seqnum = cte.seqnum + 1
     )
select *
from cte;