Ethereum Mainnet example queries

SELECT  MIN(block_number) AS `First block`,  MAX(block_number) AS `Newest block`,  COUNT(1) AS `Total number of blocks` FROM  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks; 

SELECT  TIMESTAMP_TRUNC(block_timestamp, DAY) AS timestamp1, COUNT(1) AS txn_count FROM  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions WHERE  block_timestamp >= CAST(DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH) AS TIMESTAMP) GROUP BY timestamp1 ORDER BY timestamp1 

SELECT  DATE(block_timestamp) AS block_date,  COUNT(block_number) AS daily_blocks,  7200 - COUNT(block_number) AS skipped_slots FROM  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks WHERE  DATE(block_timestamp) BETWEEN DATE("2022-09-16") AND CURRENT_DATE("UTC") - 1 /* Only count complete days after The Merge */ GROUP BY block_date 

WITH withdrawals AS (  SELECT  w.amount_lossless AS amount,  DATE(b.block_timestamp) AS block_date  FROM  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks AS b  CROSS JOIN UNNEST(withdrawals) AS w ) SELECT  block_date,  bqutil.fn.bignumber_div(bqutil.fn.bignumber_sum(ARRAY_AGG(amount)), "1000000000") AS eth_withdrawn FROM  withdrawals GROUP BY 1 ORDER BY 1 DESC 

WITH withdrawals AS (  SELECT  u.amount AS amount  FROM  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks  CROSS JOIN UNNEST(withdrawals) AS u ) SELECT  SUM(withdrawals.amount) / POW(10,9) AS total_eth_withdrawn FROM  withdrawals 

WITH tgas AS (  SELECT t.block_number, gas_used, effective_gas_price FROM  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.receipts AS r  JOIN bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions AS t  ON t.block_number = r.block_number AND t.transaction_hash = r.transaction_hash ) SELECT  /* Cast needed to avoid INT64 overflow when doing multiplication. */  SUM(CAST(tgas.effective_gas_price - b.base_fee_per_gas AS BIGNUMERIC) * tgas.gas_used) FROM  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks b JOIN tgas  ON b.block_number = tgas.block_number WHERE  b.block_number >= 12965000 /* The London hard fork. */ 

CREATE TEMP FUNCTION SlotNumber(slot_time TIMESTAMP) AS (  (SELECT DIV(TIMESTAMP_DIFF(slot_time, "2020-12-01 12:00:23 UTC", SECOND), 12)) ); CREATE TEMP FUNCTION EpochNumber(slot_time TIMESTAMP) AS (  (SELECT DIV(SlotNumber(slot_time), 32)) ); /* Beacon Chain slot timestamps. */ WITH slots AS (  /* Directly generate the first day's slots. */  SELECT * FROM UNNEST(GENERATE_TIMESTAMP_ARRAY("2020-12-01 12:00:23 UTC", "2020-12-01 23:59:59 UTC", INTERVAL 12 SECOND)) AS slot_time  UNION ALL  /* Join dates and times to generate up to yesterday's slots. Attempting this directly overflows the generator functions. */  SELECT TIMESTAMP(DATETIME(date_part, TIME(time_part))) AS slot_time  FROM UNNEST(GENERATE_DATE_ARRAY("2020-12-02", CURRENT_DATE("UTC") - 1)) AS date_part  CROSS JOIN  UNNEST(GENERATE_TIMESTAMP_ARRAY("1970-01-01 00:00:11 UTC", "1970-01-01 23:59:59 UTC", INTERVAL 12 SECOND)) AS time_part ) SELECT  EpochNumber(slot_time) AS epoch,  SlotNumber(slot_time) AS slot,  slot_time,  FORMAT("https://beaconcha.in/slot/%d", SlotNumber(slot_time)) AS beaconchain_url, FROM  slots LEFT JOIN bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks  ON slot_time = block_timestamp WHERE  block_number IS NULL AND slot_time BETWEEN "2022-09-15 06:42:59 UTC" AND CURRENT_TIMESTAMP() ORDER BY slot_time DESC; 

CREATE TEMP FUNCTION IFMINT(input STRING, ifTrue ANY TYPE, ifFalse ANY TYPE) AS (  CASE  WHEN input LIKE "0x40c10f19%" THEN ifTrue  ELSE ifFalse  END ); CREATE TEMP FUNCTION USD(input FLOAT64) AS (  CAST(input AS STRING FORMAT "$999,999,999,999") ); SELECT  DATE(block_timestamp) AS `Date`,  USD(SUM(IFMINT(input, 1, -1) * CAST(CONCAT("0x", LTRIM(SUBSTRING(input, IFMINT(input, 75, 11), 64), "0")) AS FLOAT64) / 1000000)) AS `Total Supply Change`, FROM  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions WHERE  DATE(block_timestamp) BETWEEN "2023-03-01" AND "2023-03-07"  AND to_address = "0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48" -- USDC Coin Token  AND (  input LIKE "0x42966c68%" -- Burn  OR input LIKE "0x40c10f19%" -- Mint  ) GROUP BY `Date` ORDER BY `Date` DESC; 

WITH Transfers AS (  SELECT address token, to_address account, 0 _out, CAST(quantity AS BIGNUMERIC) _in  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`  UNION ALL  SELECT address token, from_address account, CAST(quantity AS BIGNUMERIC) _out, 0 _in  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers` ) /* Top 10 Holders of USDC */ SELECT account, (SUM(_in) - SUM(_out)) / 1000000 balance FROM Transfers WHERE token = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' GROUP BY account ORDER BY balance DESC LIMIT 10; 

WITH Transfers AS (  SELECT address AS token, to_address AS account, COUNT(*) transfer_count  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`  GROUP BY token, account  UNION ALL  SELECT address AS token, from_address AS account, COUNT(*) transfer_count  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`  WHERE from_address != '0x0000000000000000000000000000000000000000'  GROUP BY token, account ) SELECT account, SUM(transfer_count) quantity FROM Transfers LEFT JOIN `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.accounts` ON account = address WHERE NOT is_contract AND token = '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d' /* BAYC */ GROUP BY account ORDER BY quantity DESC LIMIT 5; 

With Swaps AS (  SELECT block_timestamp, transaction_hash,  STRING(args[0]) sender,  STRING(args[1]) recipient,  SAFE_CAST(STRING(args[2]) AS BIGNUMERIC) amount0, /* USDC amount */  SAFE_CAST(STRING(args[3]) AS BIGNUMERIC) amount1, /* WETH amount */  SAFE_CAST(STRING(args[4]) AS BIGNUMERIC) sqrtPriceX96,  CAST(STRING(args[5]) AS BIGNUMERIC) liquidity,  CAST(STRING(args[6]) AS INT64) tick  FROM `bigquery-public-data.blockchain_analytics_ethereum_mainnet_us.decoded_events`  WHERE event_signature = 'Swap(address,address,int256,int256,uint160,uint128,int24)' /* Uniswap v3 Swaps */  AND address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' /* USDC/ETH 0.05% Pool */ ), EtherUSDC AS (  SELECT block_timestamp,  ABS(amount0) / 1000000 usdc_amount, /* USDC uses 6 decimals */  ABS(amount1) / 1000000000000000000 eth_amount, /* WETH uses 18 decimals */  ABS(SAFE_DIVIDE(amount0, amount1)) * 1000000000000 usd_eth /* USDC/ETH has 12 decimal difference */  FROM Swaps ) SELECT EXTRACT(DATE FROM block_timestamp) `date`, CAST(AVG(usd_eth) AS STRING FORMAT '$9,999.00') `avg_price`, COUNT(*) `swap_count` FROM EtherUSDC WHERE usdc_amount >= 1.00 /* Ignore miniscule swaps */ GROUP BY `date` ORDER BY `date` DESC