with all_chains_data AS (

SELECT

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,

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

MEDIAN(zeroifnull(b.price * a.decoded_log:bridgeData:minAmount / pow(10, b.decimals))) AS median_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

UNION ALL

SELECT *, 'BASE' as src_chain FROM base.core.ez_decoded_event_logs

UNION ALL

SELECT *, 'Ethereum Mainnet' as src_chain FROM ethereum.core.ez_decoded_event_logs

UNION ALL

SELECT *, 'Polygon Mainnet' as src_chain FROM polygon.core.ez_decoded_event_logs

UNION ALL

SELECT *, 'Gnosis' as src_chain FROM gnosis.core.ez_decoded_event_logs

UNION ALL

SELECT *, 'Avalanche C-Chain' as src_chain FROM avalanche.core.ez_decoded_event_logs

) AS a

LEFT JOIN crosschain.price.ez_hourly_token_prices b

ON a.decoded_log:bridgeData:sendingAssetId = b.token_address

AND date_trunc('hour', a.block_timestamp) = b.hour

WHERE

a.event_name = 'LiFiTransferStarted'

AND (a.decoded_log:bridgeData:integrator = 'jumper.exchange'

OR a.decoded_log:integrator = 'jumper.exchange'