token_in as token,

date_trunc('day', s.BLOCK_TIMESTAMP) AS date,

COUNT(DISTINCT s.TX_HASH) AS no_swaps,

COUNT(DISTINCT s.TRADER) AS no_trader,

COUNT(DISTINCT s.POOL_ID) AS no_pools,

SUM(s.AMOUNT_OUT * p.price_token) AS volume_usd

near.core.ez_dex_swaps s

LEFT JOIN (

SELECT

trunc(timestamp, 'day') AS day,

TOKEN_CONTRACT,

AVG(price_usd) AS price_token

FROM

near.core.fact_prices

GROUP BY

1,

2

) p ON s.TOKEN_OUT_CONTRACT = p.TOKEN_CONTRACT

AND s.BLOCK_TIMESTAMP::date = p.day

s.AMOUNT_OUT < 1e6

AND s.BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL '60 days'

GROUP BY

1,

2;