date_trunc('day', TIMESTAMP) as date, symbol, avg(price_usd) as price_usd

from near.core.fact_prices GROUP BY 1, 2),

df1 as (

SELECT date_trunc('day', BLOCK_TIMESTAMP) as date, tx_hash, trader, token_out, amount_in, concat(token_in,'-',token_out) as token_pair, pool_id

FROM near.core.ez_dex_swaps

WHERE platform = 'meta-token.near'),

df as (

select df1.date, df1.tx_hash, trader, pool_id, token_pair, amount_in*price_usd as volume, action_data:gas/pow(10,18) as gas

from df1 left join prices on df1.date = prices.date and df1.token_out = prices.symbol

left join near.core.fact_actions_events on df1.tx_hash = near.core.fact_actions_events.tx_hash

having volume > 0)

select date, token_pair, count(tx_hash) as tx_count, sum(volume) as volume, sum (gas) as gas

from df

group by 1,2