WITH Transactions AS (
SELECT
SENDER_WALLET,
SOURCE_CHAIN,
DESTINATION_CHAIN,
COUNT(*) AS tx_count,
SUM(STARGATE_SWAP_USD) AS total_volume_usd,
MIN(SOURCE_TIMESTAMP_UTC) AS first_tx,
MAX(SOURCE_TIMESTAMP_UTC) AS last_tx,
COUNT(DISTINCT SOURCE_CONTRACT) AS distinct_source_contracts
FROM
external.layerzero.fact_transactions_snapshot
GROUP BY
SENDER_WALLET,SOURCE_CHAIN,DESTINATION_CHAIN
),
criteria as (
SELECT
*
FROM
Transactions
WHERE
tx_count >5 -- minimo de 5 transacciones
AND total_volume_usd <100 -- menso de 100 USD de volumen
AND datediff(hour,first_tx,last_tx) < 24 -- Transacciones en menos de un día
AND distinct_source_contracts < 3 -- Pocos contratos de origen
ORDER BY
tx_count DESC
),
-- Refinamiento con correlación temporal y patrones de uso de contratos
RefinedSuspectWallets AS (
SELECT
SENDER_WALLET,
SOURCE_CHAIN,
DESTINATION_CHAIN,
COUNT(*) AS tx_count,