Plotting Ethereum Address Growth Chart in BigQuery

In my previous articles I included a challenge for plotting the number of Ethereum addresses with non-zero balance over time. Props to the guys who tackled it: 1, 2.

Below is my solution:

Run it in BigQuery

The most challenging part was to fill in the gaps for the dates on which there were no Ether transfers for some addresses. If for example, there was 1 Ether debit on Nov 1, and 1 Ether debit on Nov 3, grouping by date and summing cumulatively will give you:

  • Nov 1: 1 Ether
  • Nov 3: 2 Ether

Instead we want:

  • Nov 1: 1 Ether
  • Nov 2: 1 Ether
  • Nov 3: 2 Ether

The solution above utilizes the analytic function lead to get the next date after the gap; then the join with the calendar table to fill in the gaps.

Including Addresses with Zero Balance

If you remove where balance > 0 condition in the query, you will get the following graph:

Let me know in the comments if you know the cause of zero-balance addresses spike in the middle of October 2016.

I’d appreciate it if someone could write a kernel on Kaggle that includes the queries and charts above: https://www.kaggle.com/bigquery/ethereum-blockchain.

Challenge

Try to demonstrate Pareto distribution of Ether by plotting Lorenz curve.

Also read: