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...