Elprognerd7 - Category of the number of swappers according to the swaps count
    Updated 2023-01-04
    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
    Run a query to Download Data