ETH Price by Blocknumber

    
    WITH blockhours AS (
    
            SELECT date_trunc('hour',block_timestamp) AS blockhour, MAX(block_id) AS block_id
            FROM ethereum.udm_events
            WHERE block_timestamp >= CURRENT_DATE - 45
            GROUP BY 1
    )
    
    SELECT hour, block_id,price
    FROM blockhours
    LEFT JOIN
    ethereum.token_prices_hourly
    ON hour = blockhour
    WHERE symbol = 'WETH' -- We can use WETH to get the price of ETH from this particular table
    
    

    Prices are reported per hour in Velocity. This means in order to get to blocknumber we first look at all available transactions, group by hour and select the largest blocknumber at the time. Then we can just use a simple join to get ETH prices by hour joined into our blocknumber/hourly table.

    Loading...
    Loading...