📂Blockchain Examples
Examples about the Ethereum blockchain. Note queries will need to be modified based on situation specific for each chain.
Total Ether Supply By Day
WITH
ether_emitted_by_date AS (
SELECT
DATE(block_timestamp) AS date,
SUM(value) AS value
FROM
`bigquery-public-data.crypto_ethereum.traces`
WHERE
trace_type IN ('genesis', 'reward')
GROUP BY
DATE(block_timestamp)
)
SELECT
date,
SUM(value) OVER (ORDER BY date) / POWER(10, 18) AS supply
FROM
ether_emitted_by_date
Number Of Non-Zero Ether Ethereum Addresses
#standardSQL
-- MIT License
-- Copyright (c) 2018 Evgeny Medvedev, evge.medvedev@gmail.com
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(gas_price 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.miner, block_timestamp
UNION ALL
-- transaction fees credits
SELECT from_address AS address, -(CAST(receipt_gas_used AS numeric) * CAST(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 date, COUNT(*) AS address_count
FROM daily_balances
WHERE balance > 0
GROUP BY date
Daily Ethereum Balance by Address
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
Miner Rewards
-- *********************************************************************************************
WITH blocks_in AS (
SELECT *
FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE (DATE(timestamp ) <= DATE_ADD('2015-07-30', INTERVAL 50 DAY ) )
)
-- *********************************************************************************************
SELECT
miner,
DATE(timestamp) AS date,
COUNT(miner) AS total_block_reward
FROM
blocks_in
GROUP BY
miner,
date
HAVING
COUNT(miner) > 1
ORDER BY
dateTop Miners
#standardSQL
-- MIT License
-- Copyright (c) 2019 Yaz Khoury, yaz.khoury@gmail.com
SELECT miner,
DATE(timestamp) as date,
COUNT(miner) as total_block_reward
FROM `bigquery-public-data.crypto_ethereum_classic.blocks`
GROUP BY miner, date
HAVING COUNT(miner) > 100
ORDER BY date, COUNT(miner) ASCSource: https://github.com/YazzyYaz/Ether-Queries/blob/master/daily_top_miners.sql
Daily Hashrate Metrics
-- ***** DAILY HASHRATE in GHASH/SEC ****
-- ******************************************************************************************************************************
WITH
blocks_in AS (
SELECT *
FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE (DATE(timestamp ) <= DATE_ADD('2015-07-30', INTERVAL 7 DAY ) )
),
-- ******************************************************************************************************************************
block_rows AS (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY timestamp) AS rn
FROM
blocks_in
),
delta_time AS (
SELECT
mp.timestamp AS block_time,
mp.difficulty AS difficulty,
TIMESTAMP_DIFF(mp.timestamp, mc.timestamp, SECOND) AS delta_block_time
FROM
block_rows mc
JOIN
block_rows mp
ON
mc.rn = mp.rn - 1
),
hashrate_book AS (
SELECT
TIMESTAMP_TRUNC(block_time, DAY) AS block_day,
AVG(delta_block_time) AS daily_avg_block_time,
AVG(difficulty) AS daily_avg_difficulty
FROM
delta_time
GROUP BY
TIMESTAMP_TRUNC(block_time, DAY)
)
SELECT
DATE(block_day),
(daily_avg_difficulty/daily_avg_block_time)*POWER(10,-9) AS hashrate
FROM
hashrate_book
ORDER BY
block_day ASCSource: https://github.com/RokoMijic/awesome-bigquery-views/blob/master/views/daily_hashrate.sql
Ethereum Gini Coefficient
-- ******************************************************************************************************************************
WITH
-- remove where clauses below to unlimit the time period (will use A LOT more data ~ 200 GB!)
traces_in AS (
SELECT *
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE (DATE(block_timestamp) <= DATE_ADD('2015-07-30', INTERVAL 50 DAY ) )
),
blocks_in AS (
SELECT *
FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE (DATE(timestamp ) <= DATE_ADD('2015-07-30', INTERVAL 50 DAY ) )
),
transactions_in AS (
SELECT *
FROM `bigquery-public-data.crypto_ethereum.transactions`
WHERE (DATE(block_timestamp) <= DATE_ADD('2015-07-30', INTERVAL 50 DAY ) )
),
calendar AS (
SELECT
date
FROM
UNNEST(GENERATE_DATE_ARRAY('2015-07-30', CURRENT_DATE())) AS date
WHERE ( date <= DATE_ADD('2015-07-30', INTERVAL 50 DAY ) )
),
-- ******************************************************************************************************************************
double_entry_book AS (
SELECT
to_address AS address,
value AS value,
block_timestamp
-- debits
FROM
traces_in
WHERE TRUE
AND 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
traces_in
WHERE TRUE
AND 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(gas_price AS numeric)) AS value,
timestamp AS block_timestamp
FROM
transactions_in AS transactions
JOIN
blocks_in AS blocks
ON
blocks.number = transactions.block_number
AND blocks.timestamp = transactions.block_timestamp
GROUP BY
blocks.miner,
block_timestamp
UNION ALL
-- transaction fees credits
SELECT
from_address AS address,
-(CAST(receipt_gas_used AS numeric) * CAST(gas_price AS numeric)) AS value,
block_timestamp
FROM
transactions_in
),
double_entry_book_by_date AS (
SELECT
DATE(block_timestamp) AS date,
address,
SUM(value * power(10, -18) ) AS value
FROM
double_entry_book
GROUP BY
address,
date
),
daily_balances_with_gaps AS (
SELECT
address,
date,
SUM(value) 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_by_date
),
daily_balances AS (
SELECT
address,
calendar.date AS 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
WHERE
balance >= 0
),
address_counts AS (
SELECT
date,
COUNT(*) AS address_count
FROM
daily_balances
GROUP BY
date
),
daily_balances_sampled AS (
SELECT
address,
daily_balances.date,
balance
FROM
daily_balances
JOIN
address_counts
ON
daily_balances.date = address_counts.date
WHERE
MOD(ABS(farm_fingerprint(address)), 100000000)/100000000 <= safe_divide(10000, address_count)
),
ranked_daily_balances AS (
SELECT
date,
balance,
ROW_NUMBER() OVER (PARTITION BY date ORDER BY balance DESC) AS rank
FROM
daily_balances_sampled
)
SELECT
date,
1 - 2 * SUM((balance * (rank - 1) + balance / 2)) / COUNT(*) / SUM(balance) AS gini
FROM
ranked_daily_balances
GROUP BY
date
HAVING
SUM(balance) > 0
ORDER BY
date ASC;Source: https://github.com/RokoMijic/awesome-bigquery-views/blob/master/views/gini-coefficient-by-date.sql
Last updated