SELECT --*

--date_trunc('week', block_timestamp) as week,

--sum(CASE WHEN token_in LIKE 'wNEAR' THEN amount_in END) as wNEAR_sell,

--sum(CASE WHEN token_out LIKE 'wNEAR' THEN amount_out END) as wNEAR_buy,

--sum(CASE WHEN token_out LIKE 'wNEAR' THEN amount_out END) - sum(CASE WHEN token_in LIKE 'wNEAR' THEN amount_in END) as wNEAR_net_buy

count(DISTINCT tx_hash) as swaps,

count(DISTINCT TRADER) as traders,

sum(CASE WHEN token_in LIKE 'wNEAR' THEN amount_in ELSE amount_out END * price)

FROM near.core.ez_dex_swaps

LEFT outer JOIN (

SELECT

date_trunc('day', recorded_hour) as day,

avg(open) as price

FROM crosschain.core.fact_hourly_prices

WHERE id LIKE 'near'

GROUP BY 1

) ON day = date_trunc('day', block_timestamp)

WHERE (token_in LIKE 'wNEAR' or token_out LIKE 'wNEAR')