buzzresearchBoB daily report
    Updated 2024-08-26
    -- Daily Average Prices
    WITH DailyAveragePrices AS (
    SELECT
    DATE(hour) AS date,
    token_address,
    AVG(price) AS avg_price
    FROM ethereum.price.ez_prices_hourly
    GROUP BY 1, 2
    ),

    -- Latest Price for Specific Token
    LatestTokenPrice AS (
    SELECT
    price
    FROM ethereum.price.ez_prices_hourly
    WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
    AND price > 0
    ORDER BY hour DESC
    LIMIT 1
    ),

    -- Consolidated Transfers Data
    ConsolidatedTransfers AS (
    SELECT
    block_timestamp,
    from_address AS user,
    to_address AS pool,
    amount,
    symbol,
    contract_address
    FROM ethereum.core.ez_token_transfers
    UNION ALL
    SELECT
    block_timestamp,
    to_address AS user,
    from_address AS pool,
    QueryRunArchived: QueryRun has been archived