WITH Operations AS (
 SELECT 1 AS OperationID, 120.00 AS Amount, 'C001' AS CatalogID,
 DATE '2025年01月03日' AS OperationDate UNION ALL
 SELECT 2, 20.00, 'C001', DATE '2025年01月04日' UNION ALL
 SELECT 3, 175.00, 'C001', DATE '2025年01月05日' UNION ALL
 SELECT 4, 30.00, 'C001', DATE '2025年01月10日' UNION ALL
 SELECT 5, 190.00, 'C001', DATE '2025年01月11日' UNION ALL
 SELECT 6, 250.00, 'C001', DATE '2025年01月12日'
)
SELECT *
FROM Operations
MATCH_RECOGNIZE (
 PARTITION BY CatalogID
 ORDER BY OperationDate ASC
 MEASURES
 FIRST(OperationDate) AS START_DT,
 LAST(OperationDate) AS END_DT,
 SUM(Amount) AS TOTAL_AMOUNT,
 COUNT(*) AS ROW_COUNT
 AFTER MATCH SKIP PAST LAST ROW 
 PATTERN (low mid+ high+) 
 DEFINE
 low AS Amount < 50,
 mid AS Amount between 100 and 200,
 high AS Amount > 200
 OPTIONS ( use_longest_match = FALSE )
)
ORDER BY CatalogID, START_DT;

CatalogID	START_DT	END_DT	 TOTAL_AMOUNT	 ROW_COUNT
C001	 2025年01月10日	2025年01月12日	 470.0	 3