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