Historic Gas Price analysis

gistfile1.txt

-- this query returns the number of times the min gas has been over 500 GWEI for 200 blocks
SELECT COUNT(*) AS count
FROM (
  SELECT
    block_number,
    block_timestamp,
    MIN(gas_price / 1000000000) AS min_gas_price_gwei,
    LAG(block_number, 200) OVER (ORDER BY block_number) AS lag_10,
    LEAD(block_number, 200) OVER (ORDER BY block_number) AS lead_10
  FROM `bigquery-public-data.crypto_ethereum.transactions`
  GROUP BY block_number, block_timestamp
  HAVING MIN(gas_price / 1000000000) > 500
  ORDER BY block_number ASC
)
WHERE block_number = lag_10 + 200
  AND block_number = lead_10 - 200
  
  -- this query returns the block numbers that the min gas has been over 500 GWEI for 10 blocks
SELECT block_number
FROM (
  SELECT
    block_number,
    block_timestamp,
    MIN(gas_price / 1000000000) AS min_gas_price_gwei,
    LAG(block_number, 100) OVER (ORDER BY block_number) AS lag_10,
    LEAD(block_number, 100) OVER (ORDER BY block_number) AS lead_10
  FROM `bigquery-public-data.crypto_ethereum.transactions`
  GROUP BY block_number, block_timestamp
  HAVING MIN(gas_price / 1000000000) > 500
  ORDER BY block_number ASC
)
WHERE block_number = lag_10 + 100
  AND block_number = lead_10 - 100


-- the most expensive 21000 gas tx ever!!
SELECT  
`hash` as txid,
gas_price / 1000000000 AS fee_gwei
FROM `bigquery-public-data.crypto_ethereum.transactions`
WHERE receipt_gas_used = 21000
ORDER BY fee_gwei ASC
LIMIT 1