crypto_gostalone-yellow
    Updated 2024-10-04
    WITH BNsol AS (
    SELECT
    DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transfer_date,
    SUM(AMOUNT) AS total_volume,
    COUNT(TX_ID) AS transaction_count,
    SUM(COUNT(TX_ID)) OVER (ORDER BY DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS cumulative_tx_count
    FROM
    solana.core.fact_transfers
    WHERE
    MINT = 'BNso1VUJnh4zcfpZa6986Ea66P6TCp59hvtNJ8b1X85'
    AND BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL '7 days' -- Filter for the past 7 days
    AND BLOCK_TIMESTAMP < CURRENT_DATE
    GROUP BY
    transfer_date
    ORDER BY
    transfer_date DESC
    ),
    daily_token_prices AS (
    SELECT
    DATE_TRUNC('day', HOUR) AS price_date,
    PRICE AS closing_price,
    SYMBOL
    FROM
    solana.price.ez_prices_hourly
    WHERE
    TOKEN_ADDRESS = 'BNso1VUJnh4zcfpZa6986Ea66P6TCp59hvtNJ8b1X85'
    AND HOUR >= CURRENT_DATE - INTERVAL '7 days' -- Filter for the past 7 days
    AND HOUR < DATE_TRUNC('day', HOUR) + INTERVAL '1 day'
    QUALIFY ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('day', HOUR) ORDER BY HOUR DESC) = 1
    )
    SELECT
    BNsol.transfer_date,
    BNsol.total_volume * token_prices.closing_price AS volume_usd,
    BNsol.transaction_count,
    BNsol.cumulative_tx_count,
    token_prices.SYMBOL,
    QueryRunArchived: QueryRun has been archived