adriaparcerisasFinal List of Sybil Wallets previous to filter by Initial L0/Nansen/Chaos List
    Updated 2024-05-27
    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,
    QueryRunArchived: QueryRun has been archived