Detección de fraude: transacciones multipaís muy próximas en la misma tarjeta

WITH suivi AS (
    SELECT card_id, tx_id, tx_time, country, amount,
           LAG(country) OVER w AS pays_prec,
           LAG(tx_time) OVER w AS heure_prec
    FROM transactions
    WHERE tx_time >= now() - interval '24 hours'
    WINDOW w AS (PARTITION BY card_id ORDER BY tx_time)
)
SELECT
    card_id,
    tx_id,
    pays_prec || ' → ' || country AS saut_pays,
    to_char(tx_time - heure_prec, 'HH24:MI:SS') AS ecart,
    amount
FROM suivi
WHERE country <> pays_prec
  AND tx_time - heure_prec < interval '1 hour'
ORDER BY tx_time - heure_prec;