SELECT

trunc(block_timestamp, 'day') AS trade_date,

tx_hash,

trader,

token_in_contract,

symbol_in,

token_out_contract,

symbol_out,

amount_in,

amount_out,

amount_in_usd,

amount_out_usd,

pool_id

FROM near.defi.ez_dex_swaps

),

pricing_data AS (

SELECT

trunc(b.block_timestamp, 'day') AS price_date,

b.token_in_contract,

b.token_out_contract,

-- Price of token_in in terms of wNEAR (amount_out / amount_in when wNEAR is output)

AVG(CASE

WHEN b.symbol_out = 'wNEAR' AND b.amount_in > 0 AND b.amount_out > 0

THEN b.amount_out / b.amount_in

ELSE NULL

END) AS price_in_terms_of_wnear,

-- Price of token_out in terms of wNEAR (amount_in / amount_out when wNEAR is input)

AVG(CASE

WHEN b.symbol_in = 'wNEAR' AND b.amount_in > 0 AND b.amount_out > 0

THEN b.amount_in / b.amount_out

ELSE NULL

END) AS price_out_in_terms_of_wnear

FROM near.defi.ez_dex_swaps b