( SELECT timestamp::date AS date,

a.symbol,

b.contract_address,

b.decimals,

AVG(PRICE_USD) AS avg_price

FROM near.price.fact_prices AS a

JOIN near.core.dim_ft_contract_metadata AS b

ON a.token_contract = b.contract_address

WHERE timestamp::date >= '2024-01-01'

GROUP BY 1,2,3,4),

swap AS

( SELECT date(block_timestamp) AS date,

tx_hash,

trader,

(amount_in)*b.avg_price AS volume_in,

symbol_out,

symbol_in ,

token_in_contract,

token_out_contract,

(amount_out)*c.avg_price AS volume_out ,

CONCAT(symbol_in,'/',symbol_out) AS pair

FROM near.defi.ez_dex_swaps AS a

JOIN price AS b

ON a.block_timestamp::date = b.date

AND a.token_in_contract = b.contract_address

JOIN price AS c

ON a.TOKEN_OUT_CONTRACT = c.contract_address

AND a.block_timestamp::date = c.date

WHERE PLATFORM = 'v2.ref-finance.near'

AND block_timestamp::date >= '2024-01-01')

SELECT pair,

SUM(volume_in) AS volume

FROM swap