How can we use CTE to handle cumulative date?

Question

I want to write a recursive common table expression (CTE) in postgres to calculate a cumulative date, is it possible and how?
Many thanks.

Here's an example :

| Item | Recorded Date            | Grace period (days) | Suspension Date |
| ---- | ------------------------ | ------------------- | --------------- |
| A    | 2022-12-14 00:00:00.000  | 30                  | 2023-01-13      |
| B    | 2022-12-29 00:00:00.000  | 30                  | 2023-02-12      |
| C    | 2023-06-02 08:40:14.933  | 30                  | 2023-07-01      |
| D    | 2023-06-02 08:54:48.080  | 30                  | 2023-07-31      |
| E    | 2023-06-03 06:42:42.077  | 30                  | 2023-08-30      |

Requirements: The current suspension date is to calculated by Recorded Date or last record's suspension date

1. For item A, suspension date is calculated by 30 days after Recorded Date is 2023-01-13
2. For Item B, since last suspension date of Item A is 2023-01-13 which is larger than recorded date of Item B (ItemA’s suspension date > ItemB’s recorded date), then itemB’s suspension date would be calculated by 30 days after ItemA’s suspension date is 2023-02-12
3. Since ItemC’s recorded date is larger than ItemB’s suspension date, then ItemC’s suspension date would be calculated by 30 days after ItemC’s recorded date is 2023-07-01

Answer 1

Use a row_number() to identify the sequence of rows in the ordering of Recorded_Date

The recursive CTE will looks like

with 
cte as
(
   select Item, Recorded_Date, Grace_Period,
          rn = row_number() over (order by Recorded_Date)
   from   your_table
),
rcte as
(
  -- anchor member
  select  Item, Recorded_Date, Grace_Period,
          rn, Suspension_Date = dateadd(day, Grace_Period, Recorded_Date)
  from    cte
  where   rn = 1

  union all

  -- recursive member
  select  c.Item, c.Recorded_Date, c.Grace_Period, c.rn,
          Suspension_Date = case when r.Suspension_Date > c.Recorded_Date
                                 then dateadd(day, c.Grace_Period, r.Suspension_Date)
                                 else dateadd(day, c.Grace_Period, c.Recorded_Date)
                                 end
  from    rcte r
          inner join cte c on r.rn + 1 = c.rn 
)
select *
from   rcte