select token_address,symbol,decimals,median(price) as price

from ethereum_core.fact_hourly_token_prices

where hour::date >= current_date

group by token_address, symbol, decimals),

kashi_token_address_list as (

select event_inputs:cloneAddress::string as pair_address,'0x'||substring(event_inputs:data::string, 27, 40) as collateral,'0x'||substring(event_inputs:data::string, 91, 40) as asset

from ethereum_core.fact_event_logs

where contract_address = '0xf5bce5077908a1b7370b9ae04adc565ebd643966' and event_name = 'LogDeploy' and tx_status = 'SUCCESS' and event_removed = 'false'),

add_collateral_func as (

select tx_hash,contract_address as pair_address,event_inputs:to::string as user_address,event_inputs:share as share,event_name

from ethereum_core.fact_event_logs

where contract_address in (select pair_address from kashi_token_address_list)

and event_name in ('LogAddCollateral', 'LogAddAsset', 'LogRemoveCollateral', 'LogRemoveAsset')),

table_ as (

select block_timestamp::date as day,symbol_in as symbol, amount_in as amount

from ethereum.core.ez_dex_swaps

where tx_hash in (select tx_hash from add_collateral_func)

and symbol_in in ('USDT' , 'USDC', 'DAI', 'UST')

and day >= CURRENT_DATE - 90

select block_timestamp::date as day,symbol_out as symbol,amount_out * -1 as amount

from ethereum.core.ez_dex_swaps

where tx_hash in (select tx_hash from add_collateral_func)

and symbol_out in ('USDT' , 'USDC', 'DAI', 'UST')

and day >= CURRENT_DATE - 90

select day,symbol,abs(sum(amount)) as total_volume

from table_

group by 1, 2

order by 1