select BLOCK_TIMESTAMP::date as day

,DECODED_LOG:bridgeData:bridge as protocol

,DECODED_LOG:bridgeData:sendingAssetId as token_address

,DECODED_LOG:bridgeData:minAmount as volume

,DECODED_LOG:bridgeData:destinationChainId as dst_chain

,ORIGIN_FROM_ADDRESS

,TX_HASH

from arbitrum.core.ez_decoded_event_logs

where EVENT_NAME='LiFiTransferStarted'

and DECODED_LOG:bridgeData:integrator='jumper.exchange'

and DECODED_LOG:bridgeData:destinationChainId='80094'

),

tab2 as (

select HOUR::date as day

,TOKEN_ADDRESS

,SYMBOL

,DECIMALS

,avg(PRICE) as usd_price

from arbitrum.price.ez_prices_hourly

group by 1,2,3,4

union all

select HOUR::date as day

,'0x0000000000000000000000000000000000000000' as TOKEN_ADDRESS

,'ETH' as SYMBOL

,'18' as DECIMALS

,avg(PRICE) as usd_price

from arbitrum.price.ez_prices_hourly

where SYMBOL='WETH'

group by 1,2,3,4

select tab1.day

,sum(volume/pow(10,DECIMALS)*usd_price) as volume_bridged

,median(volume/pow(10,DECIMALS)*usd_price) as med_volume

,count(distinct TX_HASH) as bridges

,count(distinct ORIGIN_FROM_ADDRESS) as bridgers