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