• Stars
    star
    205
  • Rank 191,264 (Top 4 %)
  • Language
  • License
    MIT License
  • Created over 5 years ago
  • Updated about 2 years ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Useful SQL queries for Blockchain ETL datasets in BigQuery.

Awesome BigQuery Views

Here are some examples of how to derive insights from on-chain crypto data. Not all networks have examples here - you can find the complete list of crypto datasets in blockchain-etl/public-datasets

Top Ethereum Balances

WITH double_entry_book AS (
    -- debits
    SELECT to_address AS address, value AS value
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE to_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    UNION ALL
    -- credits
    SELECT from_address AS address, -value AS value
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE from_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    UNION ALL
    -- transaction fees debits
    SELECT 
        miner AS address, 
        SUM(CAST(receipt_gas_used AS numeric) * CAST((receipt_effective_gas_price - COALESCE(base_fee_per_gas, 0)) as numeric)) AS value
    FROM `bigquery-public-data.crypto_ethereum.transactions` AS transactions
    join `bigquery-public-data.crypto_ethereum.blocks` AS blocks ON blocks.number = transactions.block_number
    GROUP BY blocks.number, blocks.miner
    UNION ALL
    -- transaction fees credits
    SELECT 
        from_address AS address, 
        -(CAST(receipt_gas_used AS numeric) * CAST(receipt_effective_gas_price AS numeric)) AS value
    FROM `bigquery-public-data.crypto_ethereum.transactions`
)
SELECT address, SUM(value) AS balance
FROM double_entry_book
GROUP BY address
ORDER BY balance DESC
LIMIT 1000

Alternatively query bigquery-public-data.crypto_ethereum.balances (updated daily), e.g.:

SELECT *
FROM `bigquery-public-data.crypto_ethereum.balances`
WHERE SEARCH(address, '0x0cfb686e114d478b055ce8614621f8bb62f70360', analyzer=>'NO_OP_ANALYZER');

Every Ethereum Balance on Every Day

WITH double_entry_book AS (
    -- debits
    SELECT to_address AS address, value AS value, block_timestamp
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE to_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    UNION ALL
    -- credits
    SELECT from_address AS address, -value AS value, block_timestamp
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE from_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    UNION ALL
    -- transaction fees debits
    SELECT 
        miner AS address, 
        SUM(CAST(receipt_gas_used AS numeric) * CAST((receipt_effective_gas_price - COALESCE(base_fee_per_gas, 0)) AS numeric)) AS value,
        block_timestamp
    FROM `bigquery-public-data.crypto_ethereum.transactions` AS transactions
    JOIN `bigquery-public-data.crypto_ethereum.blocks` AS blocks ON blocks.number = transactions.block_number
    GROUP BY blocks.number, blocks.miner, block_timestamp
    UNION ALL
    -- transaction fees credits
    SELECT 
        from_address AS address, 
        -(CAST(receipt_gas_used AS numeric) * CAST(receipt_effective_gas_price AS numeric)) AS value,
        block_timestamp
    FROM `bigquery-public-data.crypto_ethereum.transactions`
),
double_entry_book_grouped_by_date AS (
    SELECT address, SUM(value) AS balance_increment, DATE(block_timestamp) AS date
    FROM double_entry_book
    GROUP BY address, date
),
daily_balances_with_gaps AS (
    SELECT address, date, SUM(balance_increment) OVER (PARTITION BY address ORDER BY date) AS balance,
    LEAD(date, 1, CURRENT_DATE()) OVER (PARTITION BY address ORDER BY date) AS next_date
    FROM double_entry_book_grouped_by_date
),
calendar AS (
    SELECT date FROM UNNEST(GENERATE_DATE_ARRAY('2015-07-30', CURRENT_DATE())) AS date
),
daily_balances AS (
    SELECT address, calendar.date, balance
    FROM daily_balances_with_gaps
    JOIN calendar ON daily_balances_with_gaps.date <= calendar.date AND calendar.date < daily_balances_with_gaps.next_date
)
SELECT address, date, balance
FROM daily_balances

Related article: https://medium.com/google-cloud/plotting-ethereum-address-growth-chart-55cc0e7207b2

Transaction Throughput Comparison

WITH bitcoin_throughput AS (
    -- takes transactions count in every block and divides it by average block time on that day
    SELECT 'bitcoin' AS chain, count(*) / (24 * 60 * 60 / count(*) OVER (PARTITION BY DATE(block_timestamp))) AS throughput, block_timestamp AS time
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions
    GROUP BY transactions.block_number, transactions.block_timestamp
    ORDER BY throughput DESC
    LIMIT 1
),
bitcoin_cash_throughput AS (
    SELECT 'bitcoin_cash' AS chain, count(*) / (24 * 60 * 60 / count(*) OVER (PARTITION BY DATE(block_timestamp))) AS throughput, block_timestamp AS time
    FROM `bigquery-public-data.crypto_bitcoin_cash.transactions` AS transactions
    GROUP BY transactions.block_number, transactions.block_timestamp
    ORDER BY throughput DESC
    LIMIT 1
),
ethereum_throughput AS (
    SELECT 'ethereum' AS chain, count(*) / (24 * 60 * 60 / count(*) OVER (PARTITION BY DATE(block_timestamp))) AS throughput, block_timestamp AS time
    FROM `bigquery-public-data.crypto_ethereum.transactions` AS transactions
    GROUP BY transactions.block_number, transactions.block_timestamp
    ORDER BY throughput DESC
    LIMIT 1
),
ethereum_classic_throughput AS (
    SELECT 'ethereum_classic' AS chain, count(*) / (24 * 60 * 60 / count(*) OVER (PARTITION BY DATE(block_timestamp))) AS throughput, block_timestamp AS time
    FROM `bigquery-public-data.crypto_ethereum_classic.transactions` AS transactions
    GROUP BY transactions.block_number, transactions.block_timestamp
    ORDER BY throughput DESC
    LIMIT 1
),
dogecoin_throughput AS (
    SELECT 'dogecoin' AS chain, count(*) / (24 * 60 * 60 / count(*) OVER (PARTITION BY DATE(block_timestamp))) AS throughput, block_timestamp AS time
    FROM `bigquery-public-data.crypto_dogecoin.transactions` AS transactions
    GROUP BY transactions.block_number, transactions.block_timestamp
    ORDER BY throughput DESC
    LIMIT 1
),
litecoin_throughput AS (
    SELECT 'litecoin' AS chain, count(*) / (24 * 60 * 60 / count(*) OVER (PARTITION BY DATE(block_timestamp))) AS throughput, block_timestamp AS time
    FROM `bigquery-public-data.crypto_litecoin.transactions` AS transactions
    GROUP BY transactions.block_number, transactions.block_timestamp
    ORDER BY throughput DESC
    LIMIT 1
),
dash_throughput AS (
    SELECT 'dash' AS chain, count(*) / (24 * 60 * 60 / count(*) OVER (PARTITION BY DATE(block_timestamp))) AS throughput, block_timestamp AS time
    FROM `bigquery-public-data.crypto_dash.transactions` AS transactions
    GROUP BY transactions.block_number, transactions.block_timestamp
    ORDER BY throughput DESC
    LIMIT 1
),
zcash_throughput AS (
    SELECT 'zcash' AS chain, count(*) / (24 * 60 * 60 / count(*) OVER (PARTITION BY DATE(block_timestamp))) AS throughput, block_timestamp AS time
    FROM `bigquery-public-data.crypto_zcash.transactions` AS transactions
    GROUP BY transactions.block_number, transactions.block_timestamp
    ORDER BY throughput DESC
    LIMIT 1
)
SELECT * FROM bitcoin_throughput
UNION ALL
SELECT * FROM bitcoin_cash_throughput
UNION ALL
SELECT * FROM ethereum_throughput
UNION ALL
SELECT * FROM ethereum_classic_throughput
UNION ALL
SELECT * FROM dogecoin_throughput
UNION ALL
SELECT * FROM litecoin_throughput
UNION ALL
SELECT * FROM dash_throughput
UNION ALL
SELECT * FROM zcash_throughput
ORDER BY throughput DESC

Related article: https://medium.com/@medvedev1088/comparing-transaction-throughputs-for-8-blockchains-in-google-bigquery-with-google-data-studio-edbabb75b7f1

More Queries

Network Description Query Screenshot BigQuery DataStudio Notes
Band Latest oracle prices ๐Ÿ“ ๐Ÿ”
Band Log types by transaction ๐Ÿ“ ๐Ÿ”
Bitcoin Top 1K addresses, by balance ๐Ÿ“ ๐Ÿ” ๐Ÿ“Š
Bitcoin Bitcoin Gini index, by day ๐Ÿ“ ๐Ÿ” ๐Ÿ“Š [1]
Ethereum Every account balance on every day ๐Ÿ“ ๐Ÿ” ๐Ÿ“Š [1]
Ethereum Ether supply by day ๐Ÿ“ ๐Ÿ–ผ๏ธ ๐Ÿ” ๐Ÿ“Š [1]
Ethereum Shortest path between addresses ๐Ÿ“ ๐Ÿ” โŒ
Zilliqa Shortest path between addresses v2 ๐Ÿ“ ๐Ÿ” โŒ

Check out this awesome repository: https://github.com/RokoMijic/awesome-bigquery-views

More Repositories

1

ethereum-etl

Python scripts for ETL (extract, transform and load) jobs for Ethereum blocks, transactions, ERC20 / ERC721 tokens, transfers, receipts, logs, contracts, internal transactions. Data is available in Google BigQuery https://goo.gl/oY5BCQ
Python
2,936
star
2

bitcoin-etl

ETL scripts for Bitcoin, Litecoin, Dash, Zcash, Doge, Bitcoin Cash. Available in Google BigQuery https://goo.gl/oY5BCQ
Python
405
star
3

ethereum-etl-airflow

Airflow DAGs for exporting, loading, and parsing the Ethereum blockchain data. How to get any Ethereum smart contract into BigQuery https://towardsdatascience.com/how-to-get-any-ethereum-smart-contract-into-bigquery-in-8-mins-bab5db1fdeee
Python
405
star
4

public-datasets

The list of public blockchain datasets in BigQuery
187
star
5

ethereum-etl-postgres

ETL for moving Ethereum data to PostgreSQL database
Shell
137
star
6

polygon-etl

ETL (extract, transform and load) tools for ingesting Polygon blockchain data to Google BigQuery and Pub/Sub
Python
100
star
7

blockchain-etl-streaming

Streaming Ethereum and Bitcoin blockchain data to Google Pub/Sub or Postgres in Kubernetes
Python
77
star
8

ethereum2-etl

Python scripts for ETL (extract, transform and load) jobs for Ethereum 2.0 beacon blocks, attestations, deposits, slashings, validators, committees. Data is available in Google BigQuery
Python
68
star
9

blockchain-etl-architecture

Blockchain ETL Architecture
44
star
10

ethers.js-bigquery

ethers.js library, compiled for use in Google BigQuery
JavaScript
39
star
11

solana-etl-airflow

ETL for Solana. Contributions are welcome. Join the Telegram channel https://t.me/joinchat/GsMpbA3mv1OJ6YMp3T5ORQ
Python
32
star
12

bitcoin-etl-airflow

Airflow DAGs for https://github.com/blockchain-etl/bitcoin-etl
Python
31
star
13

blockchain-kubernetes

Kubernetes manifests for running blockchain nodes
Smarty
26
star
14

ethereum-etl-neo4j

ETL for moving Ethereum data to Neo4j database
Shell
20
star
15

bigquery-to-pubsub

A tool for streaming time series data from a BigQuery table to a Pub/Sub topic
Python
16
star
16

bitcoin-etl-airflow-neo4j

Airflow DAGs for ingesting Bitcoin blockchain data to Neo4j
Python
14
star
17

tezos-etl

Python scripts for ETL (extract, transform and load) jobs for Tezos blocks, balance updates, and operations
Python
13
star
18

blockchain-etl-table-definition-cli

CLI for generating table definitions for https://github.com/blockchain-etl/ethereum-etl-airflow
Python
12
star
19

hedera-etl

ETL scripts for Hedera Hashgraph
Java
11
star
20

blockchain-streaming-analytics

Blockchain streaming analytics
Java
9
star
21

eos-etl

ETL scripts for EOS.
Python
9
star
22

ethereum-export-pipeline

UNMAINTAINED! AWS CloudFormation scripts for Ethereum ETL export pipeline
Python
8
star
23

blockchain-etl-dataflow

Dataflow pipelines for Blockchain ETL. Connects Pub/Sub topics with BigQuery tables.
Java
8
star
24

data-studio-connectors

Connect Google BigQuery crypto public datasets to Google Data Studio
JavaScript
7
star
25

abi-functions

7
star
26

blockchain-terraform-deployment

Template repository for deploying https://github.com/blockchain-etl/blockchain-terraform
HCL
6
star
27

icon-etl

Python scripts for ETL (extract, transform and load) jobs for ICON blocks, transactions, receipts, and logs.
Python
6
star
28

ethereum2-etl-airflow

Airflow DAGs for exporting Ethereum 2.0 blockchain data to Google BigQuery
Python
5
star
29

blockchain-terraform

Terraform configuration files for running blockchain nodes
HCL
5
star
30

blockchain-etl-common

Common utils for blockchain-etl
Python
5
star
31

abi

EVM public good - pull requests welcome for any ABI from any EVM
5
star
32

band-etl

ETL (extract, transform and load) tools for ingesting Band Protocol blockchain data to Google BigQuery and Pub/Sub
Python
5
star
33

tezos-kubernetes

Kubernetes manifests for running Tezos node
Shell
5
star
34

abi-parser

Web app which parses smart contracts and outputs queries and tables for Ethereum-ETL
JavaScript
5
star
35

iotex-etl

ETL (extract, transform and load) tools for ingesting IoTeX blockchain data to Google BigQuery and Pub/Sub
Python
5
star
36

ordinals-etl

Python
4
star
37

anomalous-transactions-detector-dataflow

Dataflow pipeline for detecting anomalous transactions on the Ethereum and Bitcoin blockchains
Java
4
star
38

solana-etl

Rust
4
star
39

etl-rust

Rust
4
star
40

twitter-bot-cloud-function

Google Cloud Function for tweeting Blockchain ETL alerts
JavaScript
3
star
41

zilliqa-etl

Python scripts for ETL (extract, transform and load) jobs for Zilliqa blockchain data
Python
3
star
42

pubsub-to-firestore-dataflow

Dataflow pipeline that pulls messages from a Pub/Sub topic and saves them in a Firestore collection
Java
2
star
43

eos-etl-airflow

Airflow DAGs for https://github.com/blockchain-etl/eos-etl
Python
2
star
44

bitcoin-rpc

Bitcoin JSON RPC client in Python
2
star
45

icon-etl-airflow

Airflow DAGs for exporting, loading, and parsing the ICON blockchain data.
Python
2
star
46

tezos-etl-airflow

Airflow DAGs for exporting and loading the Tezos blockchain data to Google BigQuery
Python
2
star
47

throttle-pubsub-cloud-function

Google Cloud Function that can throttle messages in a Pub/Sub topic
JavaScript
1
star
48

theta-etl

Python
1
star
49

iotex-kubernetes

Helm charts for running IoTeX node
Shell
1
star