select hour::date as day,

token_address,

avg (price) as usdprice

from avalanche.price.ez_prices_hourly

where token_address in ('0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e','0xc891eb4cbdeff6e073e859e987815ed1505c2acd') -- USDC , EUROC

group by 1,2),

xevt_deposit as (

select 'XEVT' as token,

sum (decoded_log:value/1e6) as XEVT_volume,

sum (decoded_log:value*usdprice/1e6) as USD_Volume

from avalanche.core.ez_decoded_event_logs t1 join pricet t2 on t1.block_timestamp::date = t2.day

where origin_To_address = '0xbfdef5e389bb403426337081ecd1d05bc5193203'

and origin_function_signature = '0x6e553f65' -- Deposit

and event_name = 'Transfer'

and decoded_log:from = '0x0000000000000000000000000000000000000000'

and contract_address = '0xbfdef5e389bb403426337081ecd1d05bc5193203'

and token_address = '0xc891eb4cbdeff6e073e859e987815ed1505c2acd'

and tx_succeeded),

xevt_withdraw as (

select 'XEVT' as token,

sum (decoded_log:value/1e6) as XEVT_volume,

sum (decoded_log:value*usdprice/1e6) as USD_Volume

from avalanche.core.ez_decoded_event_logs t1 join pricet t2 on t1.block_timestamp::date = t2.day

where origin_To_address = '0xbfdef5e389bb403426337081ecd1d05bc5193203'

and origin_function_signature = '0xde0c7fdf' -- Withdrawal

and event_name = 'Transfer'

and decoded_log:to = '0x0000000000000000000000000000000000000000'

and contract_address = '0xbfdef5e389bb403426337081ecd1d05bc5193203'

and token_address = '0xc891eb4cbdeff6e073e859e987815ed1505c2acd'

and tx_succeeded),

xtbt_deposit as (

select 'XTBT' as token,