from avalanche.core.fact_token_transfers

where origin_to_address = '0xe3ffc583dc176575eea7fd9df2a7c65f7e23f4c3' and origin_from_address = from_address

and date >= '2023-01-01')

,

to_token as ( select date, a.tx_hash, origin_from_address, from_token, from_amount, contract_address as to_token, raw_amount as to_amount

from avalanche.core.fact_token_transfers a join from_token b on a.tx_hash = b.tx_hash

where to_address = '0xe3ffc583dc176575eea7fd9df2a7c65f7e23f4c3'

and origin_to_address = '0xe3ffc583dc176575eea7fd9df2a7c65f7e23f4c3'

and date >= '2023-01-01'

select date, a.tx_hash, origin_from_address, from_token, from_amount, contract_address as to_token, raw_amount as to_amount

from avalanche.core.fact_token_transfers a join from_token b on a.tx_hash = b.tx_hash

where origin_to_address = '0xe3ffc583dc176575eea7fd9df2a7c65f7e23f4c3' and origin_from_address = to_address

and block_timestamp >= '2023-01-01' )

,

together as ( select date , origin_from_address, tx_hash , b.symbol as label_in, from_amount/pow(10,b.DECIMALS) as from_amounts ,

c.symbol as label_out, to_amount/pow(10,c.DECIMALS) as to_amounts

from to_token a join avalanche.core.dim_contracts b on a.from_token = b.address

join avalanche.core.dim_contracts c on a.to_token = c.address)

,

price as ( select date , label_in as symbol , (sum(to_amounts)/sum(from_amounts)) as avg_price

from together

where label_out in ('USDC') and to_amounts > 0 and from_amounts > 0

group by 1,2

select date, 'USDC' as symbol, 1 as avg_price

from together

,

volume_usd as ( select a.date, origin_from_address, tx_hash, label_in, label_out, to_amounts*b.avg_price as volume

from together a left outer join price b on a.date = b.date and a.label_out = b.symbol

left outer join price c on a.date = c.date and a.label_in = c.symbol)

,

from_token_1 as ( select date(block_timestamp) as date, tx_hash, contract_address as from_token, raw_amount as from_amount

from arbitrum.core.fact_token_transfers