Updated 2024-07-06
    WITH hourly_transactions AS (
    SELECT
    DATE_TRUNC('hour', swaps.BLOCK_TIMESTAMP) AS HOUR,
    COUNT(*) AS TOTAL_TRANSACTIONS,
    COUNT(DISTINCT swaps.SWAPPER) AS UNIQUE_HOLDERS,
    SUM(swaps.SWAP_FROM_AMOUNT_USD + swaps.SWAP_TO_AMOUNT_USD) AS TOTAL_TRANSACTION_VALUE,
    AVG((swaps.SWAP_FROM_AMOUNT_USD + swaps.SWAP_TO_AMOUNT_USD) / 2) AS AVERAGE_TRANSACTION_VALUE
    FROM
    solana.defi.ez_dex_swaps AS swaps
    WHERE
    swaps.SWAP_FROM_MINT = '3B5wuUrMEi5yATD7on46hKfej3pfmd7t1RKgrsN3pump'
    OR swaps.SWAP_TO_MINT = '3B5wuUrMEi5yATD7on46hKfej3pfmd7t1RKgrsN3pump'
    GROUP BY
    DATE_TRUNC('hour', swaps.BLOCK_TIMESTAMP)
    ),
    hourly_prices AS (
    SELECT
    DATE_TRUNC('hour', prices.INSERTED_TIMESTAMP) AS HOUR,
    AVG(prices.PRICE) AS AVERAGE_HOURLY_PRICE
    FROM
    solana.price.ez_prices_hourly AS prices
    WHERE
    prices.TOKEN_ADDRESS = '3B5wuUrMEi5yATD7on46hKfej3pfmd7t1RKgrsN3pump'
    GROUP BY
    DATE_TRUNC('hour', prices.INSERTED_TIMESTAMP)
    )
    SELECT
    ht.HOUR,
    ht.TOTAL_TRANSACTIONS,
    ht.UNIQUE_HOLDERS,
    ht.TOTAL_TRANSACTION_VALUE,
    ht.AVERAGE_TRANSACTION_VALUE,
    hp.AVERAGE_HOURLY_PRICE
    FROM
    hourly_transactions AS ht
    LEFT JOIN