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:
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 (as Thomas Jay Rush noted in the comments the spike was caused by a DDos attack).
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:
