SELECT

*

FROM

(

VALUES

('Ethereum Mainnet', '1', '0x1', 'ETH'),

('Arbitrum One', '42161', '0xa4b1', 'ETH'),

('BNB Smart Chain Mainnet', '56', '0x38', 'BNB'),

('Avalanche C-Chain', '43114', '0xa86a', 'AVAX'),

('OP Mainnet', '10', '0xa', 'ETH'),

('Polygon Mainnet', '137', '0x89', 'MATIC'),

('BASE', '8453', '0x2105', 'ETH'),

('Gnosis', '100', '0x64', 'XDAI'),

('zkSync Mainnet', '324', '0x144', 'ETH'),

('Solana', '1151111081099710', '', 'SOL'),

('Fantom Opera', '250', '0xfa', 'FTM'),

('Polygon zkEVM', '1101', '0x44d', 'ETH'),

('Linea', '59144', '0xe708', 'ETH')

) AS a (chain, dest_chain_id, chain_0x_id, currency)

),

all_chains_data AS (

SELECT

date_trunc('week', a.block_timestamp) AS date,

src_chain,

a.decoded_log:bridgeData:destinationChainId AS dest_chain_id,

count(DISTINCT a.tx_hash) AS txn_count,

count(DISTINCT a.origin_from_address) AS n_user,

sum(zeroifnull(b.price * a.decoded_log:bridgeData:minAmount / pow(10, b.decimals))) AS volume

FROM

(

SELECT *, 'Arbitrum One' as src_chain FROM arbitrum.core.ez_decoded_event_logs

UNION ALL

SELECT *, 'OP Mainnet' as src_chain FROM optimism.core.ez_decoded_event_logs

UNION ALL

SELECT *, 'BNB Smart Chain Mainnet' as src_chain FROM bsc.core.ez_decoded_event_logs