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

),

t2 as(

trader as Swapper,

sum(usd_price) as "Total Volume of Swaps",

CASE WHEN "Total Volume of Swaps" > 0 AND "Total Volume of Swaps" < 10 THEN 'Between $0 and $10'

WHEN "Total Volume of Swaps">= 10 AND "Total Volume of Swaps" < 100 THEN 'Between $10 and $100 '

WHEN "Total Volume of Swaps" >= 100 AND "Total Volume of Swaps" < 1000 THEN 'Between $100 and $1000 '

WHEN "Total Volume of Swaps" >= 1000 AND "Total Volume of Swaps" < 10000 THEN 'Between $1000 and $10000 '

ELSE 'More than $10000' END AS CAT

from t1

WHERE usd_price is not null

GROUP BY 1)

CAT,

COUNT(DISTINCT Swapper) as "Number of Swappers"

from t2

GROUP BY 1