Search code examples
sqlblockchainethereumcryptocurrency

How do I use SQL to return an average value for a day in a date range?

I'm trying to query the public Ethereum blockchain for gas data. I want my query to return the average amount of gas consumed per transaction over the course of a day, for each day since the blockchain went public to present day.

This is my query so far:

SELECT receipt_gas_used, block_timestamp, block_number
FROM `bigquery-public-data.crypto_ethereum.transactions`
WHERE DATE(block_timestamp) BETWEEN "2015-07-30" AND "2021-05-10"
LIMIT 2200

When I wrap the receipt_gas_used in the AVG function, I only get on result. When I run the query as shown above, I get numerous results for the same day. How can I write this function so that I get an average per day?

Solution

  • I think you want:

    SELECT DATE(block_timestamp) as date, AVG(receipt_gas_used)
FROM `bigquery-public-data.crypto_ethereum.transactions`
WHERE DATE(block_timestamp) BETWEEN '2015-07-30' AND '2021-05-10'
GROUP BY date
ORDER BY date;

    You probably don't need the WHERE clause, but I left it in.