📂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, [e-mail]
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
  date

Source: https://github.com/RokoMijic/awesome-bigquery-views/blob/master/views/block_rewards_daily_by_miner.sql

Top Miners

#standardSQL
-- MIT License
-- Copyright (c) 2019 Yaz Khoury, [e-mail]


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) ASC

Source: 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 ASC

Source: 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