symbol,

date_trunc('day', TIMESTAMP) AS DATE,

avg(PRICE_USD) as price

from near.core.fact_prices

GROUP BY 1, 2

ORDER BY 2),

t1 as (SELECT

TX_HASH,

BLOCK_TIMESTAMP,

TRADER,

PLATFORM,

TOKEN_IN,

AMOUNT_IN*y.price as usd_price

from near.core.ez_dex_swaps x join token_price y on x.BLOCK_TIMESTAMP::date = y.DATE AND x.TOKEN_IN = y.symbol

)

date_trunc('day', BLOCK_TIMESTAMP) as "day",

COUNT(DISTINCT tx_hash) as "Total Number of Swaps",

COUNT(DISTINCT trader) as "Total Number of Swappers",

sum(usd_price) as "Total Volume of Daily Swap (USD)",

avg(usd_price) as "Average Volume of Daily Swap (USD)"

from t1

where block_timestamp >= CURRENT_DATE - INTERVAL '14 days'

GROUP BY 1

ORDER BY 1