Business Problem

Razorpay's fraud team has noticed a spike in chargebacks and suspects a batch of cards is being tested by fraudsters running small transactions in rapid succession before attempting a large one. Build a velocity-based detection query that, for every transaction, calculates how many transactions the same user or card made in the trailing 10 minutes and the total amount charged in the trailing 60 minutes, then flags any transaction crossing known fraud thresholds (5+ transactions in 10 minutes, or over ₹500K in an hour) so the risk team can freeze the account before more damage is done. This needs to run as a rolling, per-transaction calculation rather than a daily batch summary, since fraud rings move fast.

Dataset & Schema

Transaction logs with user_id, card_id, amount, transaction_timestamp, and merchant_category columns. We need to detect users exceeding normal patterns (e.g., 5+ transactions in 10 minutes, or >₹500K in 1 hour).

CREATE TABLE transactions (
  transaction_id INTEGER PRIMARY KEY,
  user_id INTEGER,
  card_id INTEGER,
  amount DECIMAL(10,2),
  transaction_timestamp DATETIME,
  merchant_category TEXT
);

SQL Solution

WITH transaction_windows AS (
  SELECT 
    user_id,
    card_id,
    transaction_timestamp,
    amount,
    COUNT(*) OVER (
      PARTITION BY user_id 
      ORDER BY transaction_timestamp 
      RANGE BETWEEN INTERVAL '10' MINUTE PRECEDING AND CURRENT ROW
    ) as txn_count_10min,
    SUM(amount) OVER (
      PARTITION BY user_id 
      ORDER BY transaction_timestamp 
      RANGE BETWEEN INTERVAL '60' MINUTE PRECEDING AND CURRENT ROW
    ) as amount_sum_60min,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_timestamp) as txn_sequence
  FROM transactions
),
velocity_flags AS (
  SELECT 
    user_id,
    card_id,
    transaction_timestamp,
    amount,
    txn_count_10min,
    amount_sum_60min,
    CASE 
      WHEN txn_count_10min >= 5 THEN 'HIGH_FREQUENCY'
      WHEN amount_sum_60min > 500000 THEN 'HIGH_VOLUME'
      WHEN amount > (SELECT AVG(amount) * 2.5 FROM transactions) THEN 'UNUSUAL_AMOUNT'
      ELSE 'NORMAL'
    END as risk_indicator,
    CASE 
      WHEN txn_count_10min >= 5 OR amount_sum_60min > 500000 THEN 'FLAGGED'
      ELSE 'CLEAN'
    END as fraud_flag
  FROM transaction_windows
)
SELECT 
  user_id,
  card_id,
  transaction_timestamp,
  amount,
  txn_count_10min,
  amount_sum_60min,
  risk_indicator,
  fraud_flag
FROM velocity_flags
WHERE fraud_flag = 'FLAGGED'
ORDER BY user_id, transaction_timestamp;

Explanation