with token_price as (select
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
)
SELECT
trader AS "Swapper",
COUNT(DISTINCT tx_hash) as "Total Number of Swaps",
sum(usd_price) as "Total Volume of Swaps"
from t1
where usd_price is not null
GROUP BY 1
ORDER BY 3 DESC
limit 10