tx_hash,

pool_id,

trader,

amount_out,

amount_in,

amount_out_usd,

amount_in_usd,

token_in_contract,

from near.defi.ez_dex_swaps

where block_timestamp::date >= '2024-01-01'

,

price as ( select date(hour) as day,

token_address,

symbol,

avg(price) as avg_price

from near.price.ez_prices_hourly

group by 1,2,3)

,

volume_usd as ( select date,

tx_hash,

trader,

pool_id,

amount_out,

amount_in,

amount_out*b.avg_price as amt_i_usd,

amount_in*c.avg_price as amt_o_usd,

amount_out_usd,

amount_in_usd,

b.symbol as symbol_out,

c.symbol as symbol_in,

token_in_contract,

from base1 a left outer join price b on a.token_out_contract = b.token_address and a.date = b.day