How to Query Ether Supply in BigQuery

Recently we announced beta availability of transaction traces (aka internal transactions) in our public Ethereum dataset in BigQuery: https://bigquery.cloud.google.com/table/bigquery-public-data:ethereum_blockchain.traces (updated daily). They allow querying all Ethereum addresses with their balances, as well as total Ether supply.

Below is the query that retrieves total Ether supply on every date:

Run it in BigQuery

I filter out only the traces with type genesis, which include the initial Ether allocactions in block 0; and thereward type, which include both block and uncle rewards.

Below are the query results visualized in Data Studio:

From the graph you can see that on October 16th of 2017 (block 4370000) Ether supply growth rate decreased. This is associated with the block reward reduction from 5 Ether to 3 Ether, according to this EIP https://github.com/ethereum/EIPs/blob/master/EIPS/eip-1234.md

Challenge

It’s possible to query running Ether balances for every address on every date. Try to compose a query that will return the number of addresses with non-zero balance, and plot it over time. Post your SQL in the comments. (Solution)

Also read: