flipside

    REF_v1 list les event pour chaque signature
    MajorM111REF_v1 list les event pour chaque signature
    Updated 2024-12-15
    WITH contracts AS (
    -- Étape 1 : Sélection des contrats pertinents
    SELECT
    t1.address AS contract_address
    FROM
    avalanche.core.dim_contracts t1
    ORDER BY
    t1.created_block_number DESC
    LIMIT 2000000
    ),
    filtered_events AS (
    -- Étape 2 : Filtrer tous les événements associés aux contrats pertinents
    SELECT
    t1.block_number,
    t1.block_timestamp,
    t1.tx_hash,
    t1.event_index,
    t1.event_name,
    t1.decoded_log,
    t1.contract_address
    FROM
    avalanche.core.fact_decoded_event_logs t1
    WHERE
    LOWER(t1.contract_address) IN (
    SELECT LOWER(contract_address) FROM contracts -- Inclure les contrats pertinents
    )
    AND t1.tx_hash = '0x913d3688363d1bcc49489840662e21481becbbe91967dc7ef662d060adfcedc4' -- Filtrer sur le tx_hash cible
    )
    -- Étape 3 : Regrouper les événements par transaction
    SELECT
    MIN(t1.block_number) AS block_number, -- Numéro du bloc
    MIN(t1.block_timestamp) AS block_timestamp, -- Timestamp du bloc
    t1.tx_hash, -- Hash de la transaction
    LISTAGG(DISTINCT t1.contract_address, ', ') WITHIN GROUP (ORDER BY t1.contract_address) AS contract_addresses, -- Regroupe les adresses de contrats
    LISTAGG(t1.event_name, ', ') WITHIN GROUP (ORDER BY t1.event_index) AS event_names, -- Regroupe les noms d'événements dans l'ordre
    LISTAGG(TO_JSON(t1.decoded_log), ', ') WITHIN GROUP (ORDER BY t1.event_index) AS decoded_logs -- Regroupe les logs décodés sous forme JSON
    QueryRunArchived: QueryRun has been archived