real-time fraud and anomaly detection.sql

-- production fraud and alerting job
SELECT *
FROM paymentauths
MATCH_RECOGNIZE(
  PARTITION BY card
  ORDER BY eventTimestamp
  MEASURES
      F.amount AS first_amount,
      E.amount AS last_amount
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (F+ E)                                        -- match 1 or more rows
  DEFINE
    F AS F.amount IS NOT NULL AND F.amount > 10,        -- lower boundary
    E AS E.amount IS NOT NULL AND F.amount < E.amount)  -- starting value less than ending value