Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (ID, Period, "DATE") AS
SELECT 1, 1, DATE '2020-09-05' FROM DUAL UNION ALL
SELECT 1, 2, DATE '2020-10-01' FROM DUAL UNION ALL
SELECT 1, 3, DATE '2020-10-30' FROM DUAL UNION ALL
SELECT 2, 2, DATE '2020-10-01' FROM DUAL UNION ALL
SELECT 2, 4, DATE '2020-12-05' FROM DUAL UNION ALL
SELECT 2, 6, DATE '2021-02-05' FROM DUAL UNION ALL
SELECT 3, 1, DATE '2020-09-05' FROM DUAL
7 rows affected

SELECT id,
       period,
       "DATE",
       counter
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY id
  ORDER     BY "DATE"
  MEASURES
    COUNT(*) AS counter
  ALL ROWS PER MATCH
  PATTERN (any_date streak_date*)
  DEFINE
    streak_date AS "DATE" <= PREV("DATE") + INTERVAL '30' DAY
              -- AND PREV(period) < period
)


ID
PERIOD
DATE
COUNTER


1
1
2020-09-05 00:00:00
1

1
2
2020-10-01 00:00:00
2

1
3
2020-10-30 00:00:00
3

2
2
2020-10-01 00:00:00
1

2
4
2020-12-05 00:00:00
1

2
6
2021-02-05 00:00:00
1

3
1
2020-09-05 00:00:00
1

ID	PERIOD	DATE	COUNTER
1	1	2020-09-05 00:00:00	1
1	2	2020-10-01 00:00:00	2
1	3	2020-10-30 00:00:00	3
2	2	2020-10-01 00:00:00	1
2	4	2020-12-05 00:00:00	1
2	6	2021-02-05 00:00:00	1
3	1	2020-09-05 00:00:00	1