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 (trans_id, customer_id, trans_date) AS
SELECT '001', '1101', DATE '2020-11-02' FROM DUAL UNION ALL
SELECT '002', '1101', DATE '2020-11-14' FROM DUAL UNION ALL
SELECT '003', '1101', DATE '2020-11-18' FROM DUAL UNION ALL
SELECT '004', '1101', DATE '2021-12-04' FROM DUAL UNION ALL
SELECT '005', '1101', DATE '2021-12-05' FROM DUAL UNION ALL
SELECT '006', '1101', DATE '2021-12-08' FROM DUAL UNION ALL
SELECT '007', '1101', DATE '2021-01-17' FROM DUAL UNION ALL
SELECT '008', '1101', DATE '2021-05-01' FROM DUAL UNION ALL
SELECT '009', '1101', DATE '2021-05-04' FROM DUAL UNION ALL
SELECT '010', '1102', DATE '2021-03-02' FROM DUAL UNION ALL
SELECT '011', '1102', DATE '2021-03-08' FROM DUAL UNION ALL
SELECT '012', '1102', DATE '2021-04-01' FROM DUAL UNION ALL
SELECT '013', '1102', DATE '2021-04-02' FROM DUAL UNION ALL
SELECT '014', '1102', DATE '2021-04-12' FROM DUAL UNION ALL
SELECT '015', '1102', DATE '2021-04-29' FROM DUAL UNION ALL
SELECT '016', '1102', DATE '2021-06-10' FROM DUAL UNION ALL
SELECT '017', '1102', DATE '2021-06-12' FROM DUAL;
17 rows affected
SELECT *
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY customer_id
ORDER BY trans_date
MEASURES
MATCH_NUMBER() AS grp
ALL ROWS PER MATCH
PATTERN ( within_30_days+ )
DEFINE
within_30_days AS trans_date <= FIRST(trans_date) + INTERVAL '30' DAY
)
| CUSTOMER_ID | TRANS_DATE | GRP | TRANS_ID |
|---|---|---|---|
| 1101 | 2020-11-02 00:00:00 | 1 | 001 |
| 1101 | 2020-11-14 00:00:00 | 1 | 002 |
| 1101 | 2020-11-18 00:00:00 | 1 | 003 |
| 1101 | 2021-01-17 00:00:00 | 2 | 007 |
| 1101 | 2021-05-01 00:00:00 | 3 | 008 |
| 1101 | 2021-05-04 00:00:00 | 3 | 009 |
| 1101 | 2021-12-04 00:00:00 | 4 | 004 |
| 1101 | 2021-12-05 00:00:00 | 4 | 005 |
| 1101 | 2021-12-08 00:00:00 | 4 | 006 |
| 1102 | 2021-03-02 00:00:00 | 1 | 010 |
| 1102 | 2021-03-08 00:00:00 | 1 | 011 |
| 1102 | 2021-04-01 00:00:00 | 1 | 012 |
| 1102 | 2021-04-02 00:00:00 | 2 | 013 |
| 1102 | 2021-04-12 00:00:00 | 2 | 014 |
| 1102 | 2021-04-29 00:00:00 | 2 | 015 |
| 1102 | 2021-06-10 00:00:00 | 3 | 016 |
| 1102 | 2021-06-12 00:00:00 | 3 | 017 |