LI.FIEZ transactions search
    Updated 2024-08-20
    -- forked from Integrators Base @ https://flipsidecrypto.xyz/edit/queries/c62b55c9-b576-4be4-8daa-40cd6dce5acf

    WITH chains AS (
    SELECT
    *
    FROM
    (
    VALUES
    ('Ethereum', '1', '0x1', 'ETH'),
    ('Arbitrum', '42161', '0xa4b1', 'ETH'),
    ('BSC', '56', '0x38', 'BNB'),
    ('Avalanche', '43114', '0xa86a', 'AVAX'),
    ('OP', '10', '0xa', 'ETH'),
    ('Polygon', '137', '0x89', 'MATIC'),
    ('BASE', '8453', '0x2105', 'ETH'),
    ('Gnosis', '100', '0x64', 'XDAI'),
    ('zkSync', '324', '0x144', 'ETH'),
    ('Solana', '1151111081099710', '', 'SOL'),
    ('Fantom', '250', '0xfa', 'FTM'),
    ('Polygon zkEVM', '1101', '0x44d', 'ETH'),
    ('Linea', '59144', '0xe708', 'ETH')
    ) AS a (chain, dest_chain_id, chain_domain_id, currency)
    )
    ,
    all_bridge_txns as
    (
    SELECT *, 'Arbitrum' as src_chain, replace(decoded_log:bridgeData:sendingAssetId, '0x0000000000000000000000000000000000000000', '0x82af49447d8a07e3bd95bd0d56f35241523fbab1') AS token_address FROM arbitrum.core.ez_decoded_event_logs
    WHERE decoded_log:bridgeData:integrator = '{{integrator}}'
    UNION ALL
    SELECT *, 'OP' as src_chain, replace(decoded_log:bridgeData:sendingAssetId, '0x0000000000000000000000000000000000000000', '0x4200000000000000000000000000000000000006') AS token_address FROM optimism.core.ez_decoded_event_logs
    WHERE decoded_log:bridgeData:integrator = '{{integrator}}'
    UNION ALL
    SELECT *, 'BSC' as src_chain , replace(decoded_log:bridgeData:sendingAssetId, '0x0000000000000000000000000000000000000000', '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c') AS token_address FROM bsc.core.ez_decoded_event_logs
    WHERE decoded_log:bridgeData:integrator = '{{integrator}}'
    UNION ALL
    SELECT *, 'BASE' as src_chain, replace(decoded_log:bridgeData:sendingAssetId, '0x0000000000000000000000000000000000000000', '0x4200000000000000000000000000000000000006') AS token_address FROM base.core.ez_decoded_event_logs
    QueryRunArchived: QueryRun has been archived