Crypto Ethereum SQL queries

crypto bigquery python analysis
Author

geeknees

Published

April 24, 2024

!pip install bigquery
!pip install db-dtypes
Requirement already satisfied: bigquery in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (0.0.41)
Requirement already satisfied: dbstream>=0.1.19 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (0.1.25)
Requirement already satisfied: google-cloud-bigquery>=2.4.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (3.21.0)
Requirement already satisfied: googleauthentication>=0.0.12 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (0.0.17)
Requirement already satisfied: google-cloud-bigquery-storage>=2.1.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (2.24.0)
Requirement already satisfied: pandas==1.3.4 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (1.3.4)
Requirement already satisfied: pyarrow>=2.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (16.0.0)
Requirement already satisfied: python-dateutil>=2.7.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas==1.3.4->bigquery) (2.9.0.post0)
Requirement already satisfied: pytz>=2017.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas==1.3.4->bigquery) (2024.1)
Requirement already satisfied: numpy>=1.21.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas==1.3.4->bigquery) (1.26.4)
Requirement already satisfied: dacktool>=0.0.7 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from dbstream>=0.1.19->bigquery) (0.0.7)
Requirement already satisfied: requests>=2.22.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from dbstream>=0.1.19->bigquery) (2.31.0)
Requirement already satisfied: google-api-core>=2.3.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from dbstream>=0.1.19->bigquery) (2.18.0)
Requirement already satisfied: google-auth<3.0.0dev,>=2.14.1 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery>=2.4.0->bigquery) (2.29.0)
Requirement already satisfied: google-cloud-core<3.0.0dev,>=1.6.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery>=2.4.0->bigquery) (2.4.1)
Requirement already satisfied: google-resumable-media<3.0dev,>=0.6.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery>=2.4.0->bigquery) (2.7.0)
Requirement already satisfied: packaging>=20.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery>=2.4.0->bigquery) (24.0)
Requirement already satisfied: proto-plus<2.0.0dev,>=1.22.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery-storage>=2.1.0->bigquery) (1.23.0)
Requirement already satisfied: protobuf!=3.20.0,!=3.20.1,!=4.21.0,!=4.21.1,!=4.21.2,!=4.21.3,!=4.21.4,!=4.21.5,<5.0.0dev,>=3.19.5 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery-storage>=2.1.0->bigquery) (4.25.3)
Requirement already satisfied: google-api-python-client==1.7.11 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (1.7.11)
Requirement already satisfied: google-auth-httplib2==0.0.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (0.0.3)
Requirement already satisfied: google-auth-oauthlib>=0.4.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (1.2.0)
Requirement already satisfied: cryptography>=2.7 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (42.0.5)
Requirement already satisfied: google-cloud-secret-manager==2.7.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (2.7.2)
Requirement already satisfied: httplib2<1dev,>=0.9.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-python-client==1.7.11->googleauthentication>=0.0.12->bigquery) (0.22.0)
Requirement already satisfied: six<2dev,>=1.6.1 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-python-client==1.7.11->googleauthentication>=0.0.12->bigquery) (1.16.0)
Requirement already satisfied: uritemplate<4dev,>=3.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-python-client==1.7.11->googleauthentication>=0.0.12->bigquery) (3.0.1)
Requirement already satisfied: grpc-google-iam-v1<0.13dev,>=0.12.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-secret-manager==2.7.2->googleauthentication>=0.0.12->bigquery) (0.12.7)
Requirement already satisfied: libcst>=0.2.5 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-secret-manager==2.7.2->googleauthentication>=0.0.12->bigquery) (1.3.1)
Requirement already satisfied: cffi>=1.12 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from cryptography>=2.7->googleauthentication>=0.0.12->bigquery) (1.16.0)
Requirement already satisfied: googleapis-common-protos<2.0.dev0,>=1.56.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-core>=2.3.2->dbstream>=0.1.19->bigquery) (1.63.0)
Requirement already satisfied: grpcio<2.0dev,>=1.33.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-cloud-bigquery>=2.4.0->bigquery) (1.62.2)
Requirement already satisfied: grpcio-status<2.0.dev0,>=1.33.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-cloud-bigquery>=2.4.0->bigquery) (1.62.2)
Requirement already satisfied: cachetools<6.0,>=2.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-auth<3.0.0dev,>=2.14.1->google-cloud-bigquery>=2.4.0->bigquery) (5.3.3)
Requirement already satisfied: pyasn1-modules>=0.2.1 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-auth<3.0.0dev,>=2.14.1->google-cloud-bigquery>=2.4.0->bigquery) (0.4.0)
Requirement already satisfied: rsa<5,>=3.1.4 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-auth<3.0.0dev,>=2.14.1->google-cloud-bigquery>=2.4.0->bigquery) (4.9)
Requirement already satisfied: requests-oauthlib>=0.7.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-auth-oauthlib>=0.4.0->googleauthentication>=0.0.12->bigquery) (2.0.0)
Requirement already satisfied: google-crc32c<2.0dev,>=1.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-resumable-media<3.0dev,>=0.6.0->google-cloud-bigquery>=2.4.0->bigquery) (1.5.0)
Requirement already satisfied: charset-normalizer<4,>=2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests>=2.22.0->dbstream>=0.1.19->bigquery) (3.3.2)
Requirement already satisfied: idna<4,>=2.5 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests>=2.22.0->dbstream>=0.1.19->bigquery) (3.7)
Requirement already satisfied: urllib3<3,>=1.21.1 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests>=2.22.0->dbstream>=0.1.19->bigquery) (2.2.1)
Requirement already satisfied: certifi>=2017.4.17 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests>=2.22.0->dbstream>=0.1.19->bigquery) (2024.2.2)
Requirement already satisfied: pycparser in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from cffi>=1.12->cryptography>=2.7->googleauthentication>=0.0.12->bigquery) (2.22)
Requirement already satisfied: pyparsing!=3.0.0,!=3.0.1,!=3.0.2,!=3.0.3,<4,>=2.4.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from httplib2<1dev,>=0.9.2->google-api-python-client==1.7.11->googleauthentication>=0.0.12->bigquery) (3.1.2)
Requirement already satisfied: pyyaml>=5.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from libcst>=0.2.5->google-cloud-secret-manager==2.7.2->googleauthentication>=0.0.12->bigquery) (6.0.1)
Requirement already satisfied: pyasn1<0.7.0,>=0.4.6 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pyasn1-modules>=0.2.1->google-auth<3.0.0dev,>=2.14.1->google-cloud-bigquery>=2.4.0->bigquery) (0.6.0)
Requirement already satisfied: oauthlib>=3.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.0->googleauthentication>=0.0.12->bigquery) (3.2.2)
Requirement already satisfied: db-dtypes in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (1.2.0)
Requirement already satisfied: packaging>=17.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from db-dtypes) (24.0)
Requirement already satisfied: pandas>=0.24.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from db-dtypes) (1.3.4)
Requirement already satisfied: pyarrow>=3.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from db-dtypes) (16.0.0)
Requirement already satisfied: numpy>=1.16.6 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from db-dtypes) (1.26.4)
Requirement already satisfied: python-dateutil>=2.7.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas>=0.24.2->db-dtypes) (2.9.0.post0)
Requirement already satisfied: pytz>=2017.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas>=0.24.2->db-dtypes) (2024.1)
Requirement already satisfied: six>=1.5 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from python-dateutil>=2.7.3->pandas>=0.24.2->db-dtypes) (1.16.0)
#Importing necessary modules
from google.cloud import bigquery
from google.oauth2 import service_account
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import os
%matplotlib inline

# Create a "Client" object
key_path = os.environ['BIGQUERY_SERVICE_ACCOUNT_AUTH_FILE_PATH']
credentials = service_account.Credentials.from_service_account_file(key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"])
client = bigquery.Client(credentials=credentials, project=credentials.project_id)
# Construct a reference to the "Ethereum Blockchain" dataset
eth_ref = client.dataset("crypto_ethereum", project="bigquery-public-data")

# API request - fetch the dataset
dataset = client.get_dataset(eth_ref)

# List all the tables in the "crypto_ethereum" dataset
tables = list(client.list_tables(dataset))

# Print names of all tables in the dataset
for table in tables:
    print(table.table_id)
amended_tokens
balances
blocks
contracts
load_metadata
logs
sessions
token_transfers
tokens
traces
transactions

Use a function to show amount of data to be scanned for a query not to exceed the limit for a Kaggle user, before actually running the query

def show_amount_of_data_scanned(query):
    # dry_run lets us see how much data the query uses without running it
    dry_run_config = bigquery.QueryJobConfig(dry_run=True)
    query_job = client.query(query, job_config=dry_run_config)
    print('Data processed: {} GB'.format(round(query_job.total_bytes_processed / 10**9, 3)))

The ethereum blockchain is fairly popular with users, the query below will show the top 10 wallet sender addresses with the most number of transactions

transaction_table_ref = eth_ref.table("transactions")
transaction_table = client.get_table(transaction_table_ref)

most_transaction_query = """
                 SELECT from_address as wallet_address, COUNT(*) AS transactions
                 FROM `bigquery-public-data.crypto_ethereum.transactions`
                 WHERE EXTRACT(YEAR from block_timestamp) = 2024
                 GROUP BY wallet_address
                 ORDER BY transactions DESC
                 LIMIT 10
                 """

Firstly, check amount of data scanned

show_amount_of_data_scanned(most_transaction_query)
Data processed: 8.633 GB

Run the query

query_job = client.query(most_transaction_query)

addresses_with_most_transactions = query_job.to_dataframe()

addresses_with_most_transactions
wallet_address transactions
0 0x75e89d5979e4f6fba9f97c104c2f0afb3f1dcb88 1782754
1 0xf89d7b9c864f589bbf53a82105107622b35eaa40 1266789
2 0x0d0707963952f2fba59dd06f2b425ace40b492fe 1194395
3 0x46340b20830761efd32832a74d7169b29feb9758 1143232
4 0x21a31ee1afc51d94c2efccaa2092ad1028285549 1134223
5 0x28c6c06298d514db089934071355e5743bf21d60 1123122
6 0xdfd5293d8e347dfe59e90efd55b2956a1343963d 1093943
7 0xae2fc483527b8ef99eb5d9b44875f005ba1fae13 805327
8 0x9696f59e4d72e237be84ffd425dcad154bf96976 694829
9 0x974caa59e49682cda0ad2bbe82983419a2ecc400 670332

The ethereum blockchain is also known for its high gas prices, the query below will show the top 10 highest gas fees of the dataset

highest_gas_query = """
                 SELECT block_hash, block_timestamp, receipt_cumulative_gas_used
                 FROM `bigquery-public-data.crypto_ethereum.transactions`
                 WHERE EXTRACT(YEAR from block_timestamp) = 2024
                 ORDER BY receipt_cumulative_gas_used DESC
                 LIMIT 10
                 """
query_job = client.query(highest_gas_query)

highest_gas = query_job.to_dataframe()

highest_gas
block_hash block_timestamp receipt_cumulative_gas_used
0 0xd63e815a03a0b9e7be87d81bad646137473ef7e94c5a... 2024-05-11 01:39:11+00:00 30029206
1 0xad5a9de8a918b61437c2bfc6a65cec33a010afb08482... 2024-05-14 02:48:59+00:00 30029204
2 0x3b3eb42ce1ae1a38e238853cee998ff56dddaefbd102... 2024-04-20 12:46:11+00:00 30029204
3 0xecec1ff6186785bb0f74b4f3a5537a659d440d778bcc... 2024-04-22 03:34:23+00:00 30029192
4 0x3d6fdf73cb6976545182538542ae1bcc5e72990463d0... 2024-03-15 13:10:11+00:00 30029024
5 0xdacc31fe2b70a85722b5aefa90fcc50e44bb23e9dc5e... 2024-04-29 02:20:23+00:00 30028841
6 0x7244d9b8c76835da19c8849e8623f746e9ba0b91dc47... 2024-04-12 02:26:35+00:00 30028833
7 0x6adae77775bb702a110a08a46aaef2811beb0a75e071... 2024-05-18 06:18:23+00:00 30028766
8 0x2f31698831688a5c5cc903a661c2396a6a7dbf4dea89... 2024-05-08 09:27:47+00:00 30028630
9 0x43933205a01a9a6756f296e2b2db7146150046dde6ff... 2024-05-11 12:03:47+00:00 30028599

Let’s see the dates and the number of transactions in year 2024 using an analytic function

transfer_date_query = """
                      WITH transfer_a_day AS
                      (
                      SELECT DATE(block_timestamp) AS token_transfer_date,
                          COUNT(*) as num_transfer
                      FROM `bigquery-public-data.crypto_ethereum.token_transfers`
                      WHERE EXTRACT(YEAR from block_timestamp) = 2024
                      GROUP BY token_transfer_date
                      )
                      SELECT token_transfer_date,
                          SUM(num_transfer)
                              OVER (
                                  ORDER BY num_transfer DESC
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                              ) AS sum_transactions
                              FROM transfer_a_day
                     """

Firstly, check amount of data scanned

show_amount_of_data_scanned(transfer_date_query)
Data processed: 1.415 GB

Run the query

query_job = client.query(transfer_date_query)

transfer_date = query_job.to_dataframe()

transfer_date.head()
token_transfer_date sum_transactions
0 2024-01-22 90060745
1 2024-05-20 176925824
2 2024-02-25 116016741
3 2024-05-02 113584611
4 2024-01-07 176143595

ref. https://www.kaggle.com/code/akafieva/crypto-ethereum-sql-queries