Group customer transactions based on the subsequent transaction

Small change in the requirement

Needed Teradata SQL query to create a grouping from the first transaction to its subsequent transactions. If a subsequent transaction is made within 30 days of the previous transaction, consider them all to belongs to the same group. Break the group and start a new one if any transaction is made more than 30 days after the previous transaction.

data:

Expected result (expecting grouping like below). from 001 till 004 fall under within 30 day to its previous transaction. 005 has elapsed 30 day from it's previous transaction so new group has to be started.

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row pattern matching:

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
)

Which, for the sample data:

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;

Outputs:

fiddle