Postgres 14

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

CREATE TABLE

INSERT 0 12

with recursive
  -- Identify unambiguous window starts: those with no previous ts in the 7 previous days.
  maybe as
  (
    select
      *,
      row_number() over w num, -- Will ease our reading of results.
      -- startpoint:
      -- - true: confirmed start of a new window
      -- - null: maybe, maybe not; will be later decided (depending on if the previous ts (nearer than 7 days ago), has itself been eaten by a previous window (thus let us be a new start) or not (then the previous is a start and we're eaten by it)).
      case when lag(enroll_date) over w > enroll_date - interval '30d' then null else true end startpoint
    from plan
    window w as (partition by customer_id order by enroll_date)
  ),
  -- Continents of ts never more than 7 days far one from another.
  c as
  (
    select
      *,
      -- Identify it by the num of the unambiguous starting point.
      max(case when startpoint then num end) over w continent,
      -- Now attributes for *hypothetical* new island starts:
      -- for each ts, choose its successor in case this one becomes an island start
      -- The successor is the first row from the same continent, but further than 7 days from this one.
      min(num) over (w range between '30d' following and unbounded following) successor,
      -- Number of rows which would belong to this 7 days window (in case the current row is a window start).
      count(1) over (w range between current row and '30d' following) n_included
    from maybe
    window w as (partition by customer_id order by enroll_date)
  ),
  -- Now iterate starting from the continents,
  -- to see if we can determine islands within them.
  -- (each start of island "eats" the 7 following days, so the first row after 7 days can be elected as the start of a new island)
  i as
  (
    select * from c where startpoint
    union
    select next.*
    -- Do not forget to filter on island, as successor has been computed without this criteria (before we had determined islands).
    from i join c next using (customer_id, continent)
    where next.num = i.successor
  )
select plan.*, i.type, coalesce(i.type, 'duplicate') = plan.type real_equal_expected
from plan left join i using (customer_id, enroll_date)
order by customer_id, enroll_date;

customer_id	enroll_date	type	type	real_equal_expected
1	2024-03-05	valid	valid	t
1	2024-05-21	valid	valid	t
1	2024-06-09	duplicate	null	t
1	2024-06-21	valid	valid	t
1	2024-07-04	duplicate	null	t
1	2024-07-16	duplicate	null	t
1	2024-07-26	valid	valid	t
1	2024-08-03	duplicate	null	t
1	2024-08-17	duplicate	null	t
1	2024-09-05	valid	valid	t
2	2024-02-05	valid	valid	t
2	2024-02-25	duplicate	null	t

SELECT 12