Skip to content

Instantly share code, notes, and snippets.

@adietrichs

adietrichs/blocks.sql

Created December 10, 2021 00:19
Show Gist options
  • Save adietrichs/9837f96d44603a76349fd98525c7cb43 to your computer and use it in GitHub Desktop.
Save adietrichs/9837f96d44603a76349fd98525c7cb43 to your computer and use it in GitHub Desktop.
Download ZIP
EIP-4488 Optimal Mining Analysis
Raw
blocks.sql
SELECT
t.number,
t.gas_limit,
t.base_fee_per_gas
FROM
`bigquery-public-data.crypto_ethereum.blocks` AS t
WHERE
t.timestamp >= "2021-11-30 00:00:00"
AND t.timestamp < "2021-12-07 00:00:00"
AND MOD(t.number, 10) = 1
ORDER BY
t.number;
Raw
transactions.sql
CREATE TEMP FUNCTION
calcInputCost(input STRING)
RETURNS INT64
LANGUAGE js AS "return input == '0x' ? 0 : input.match(/([0-9a-f]{2})/g).map(b => b == '00' ? 4 : 16).reduce((a, b) => a+b);";
SELECT
DIV(BYTE_LENGTH(t.input), 2) - 1 AS input_length,
calcInputCost(t.input) AS input_cost,
t.receipt_gas_used,
t.block_number,
t.receipt_effective_gas_price
FROM
`bigquery-public-data.crypto_ethereum.transactions` AS t
WHERE
t.block_timestamp >= "2021-11-30 00:00:00"
AND t.block_timestamp < "2021-12-07 00:00:00"
AND MOD(t.block_number, 10) = 1
ORDER BY
t.block_number,
t.transaction_index;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment