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
),
t2 as(
SELECT
trader as Swapper,
COUNT(DISTINCT tx_hash) as "Total Number of Swaps",
CASE WHEN "Total Number of Swaps" =1 THEN 'Just One Swap'
WHEN "Total Number of Swaps" > 1 AND "Total Number of Swaps" < 10 THEN 'Between 2 and 10 Swaps'
WHEN "Total Number of Swaps">= 10 AND "Total Number of Swaps" < 100 THEN 'Between 10 and 100 Swaps'
ELSE 'More than 100 Swaps' END AS CAT
from t1
GROUP BY 1)
SELECT
CAT,
COUNT(DISTINCT Swapper) as "Number of Swappers"
from t2
GROUP BY 1