SELECT *
FROM daily_sales
MATCH_RECOGNIZE (
  PARTITION BY store_id          -- 1. How to group the data
  ORDER BY sales_date            -- 2. How to sort the data
  MEASURES                       -- 3. What columns to output
    STRT.sales_date AS start_date,
    LAST(UP.sales_date) AS end_date
  ONE ROW PER MATCH              -- 4. Output granularity (1 row per pattern vs all rows)
  AFTER MATCH SKIP TO LAST UP    -- 5. Where to start searching for the next pattern
  PATTERN (STRT DOWN+ UP+)       -- 6. The Regex Pattern!
  DEFINE                         -- 7. Define what the variables mean
    DOWN AS DOWN.sales_amt < PREV(DOWN.sales_amt),
    UP   AS UP.sales_amt > PREV(UP.sales_amt)
)

SELECT item_id, stockout_start, recovery_date, duration_days
FROM inventory_history
MATCH_RECOGNIZE (
  PARTITION BY item_id
  ORDER BY snapshot_date
  MEASURES
    STRT.snapshot_date AS stockout_start,
    RECOVER.snapshot_date AS recovery_date,
    MATCH_NUMBER() AS match_id,
    COUNT(NEGATIVE.*) AS duration_days
  ONE ROW PER MATCH
  PATTERN (STRT NEGATIVE+ RECOVER)
  DEFINE
    STRT     AS STRT.soh < 0,
    NEGATIVE AS NEGATIVE.soh < 0,
    RECOVER  AS RECOVER.soh >= 0
);

SELECT user_id, session_id, start_time, end_time, page_views
FROM clickstream
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY click_time
  MEASURES
    MATCH_NUMBER() AS session_id,
    FIRST(click_time) AS start_time,
    LAST(click_time) AS end_time,
    COUNT(*) AS page_views
  ONE ROW PER MATCH
  PATTERN (STRT CLICKS*)
  DEFINE
    CLICKS AS CLICKS.click_time - PREV(CLICKS.click_time) <= INTERVAL '30' MINUTE
);

SELECT start_gap, end_gap
FROM invoices
MATCH_RECOGNIZE (
  ORDER BY invoice_id
  MEASURES
    STRT.invoice_id + 1 AS start_gap,
    NEXT_VAL.invoice_id - 1 AS end_gap
  ONE ROW PER MATCH
  PATTERN (STRT NEXT_VAL)
  DEFINE
    NEXT_VAL AS NEXT_VAL.invoice_id > PREV(NEXT_VAL.invoice_id) + 1
);