Blocks

The Ethereum Blocks Table is Raw Dataset that track and display detailed information about each block mined/validated on the Ethereum network. This table serves as a comprehensive record of all blocks processed and includes a variety of data points essential for understanding and analyzing the network's activity. Our Raw ethereum_blocks dataset consist of the following columns with the corresponding data types:

Data Types

Columns	Data Types
timestamp	timestamp
number	bigint
hash	string
parent_hash	string
nonce	string
sha3_uncles	string
logs_bloom	string
transactions_root	string
state_root	string
receipts_root	string
miner	string
difficulty	double
total_difficulty	double
size	bigint
extra_data	string
gas_limit	bigint
gas_used	bigint
transaction_count	bigint
base_fee_per_gas	bigint

Key Descriptions of Columns in Ethereum Blocks Table

1. timestamp: The exact date and time when the block was mined. This helps in tracking the block creation rate and understanding network activity over time.

2. number: A unique identifier for each block in the blockchain. It indicates the block’s position in the entire chain, starting from the genesis block.

3. hash: A unique hash of the block. It serves as the block’s fingerprint and is used to securely link blocks in cryptographic chains.

4. parent_hash: The hash of the previous block in the chain. This links each block to its predecessor, forming the blockchain.

5. nonce: A value that miners had to find in order to successfully mine the block. It proves that the necessary work was done to mine the block.

6. sha3_uncles: SHA-3 hash of the RLP (Recursive Length Prefix) encoded list of uncle blocks included in the block. It verifies that specific uncles are part of the block without needing to include all their data in the header. You can read more about Uncle Blocks in our blogs.

7. logs_bloom: A data field used to filter and search for events emitted by smart contracts during contract execution. The logs_bloom field are constructed using hashes of logs topics.

8. transactions_root: Root hash of Merkle Patricia Trie (MPT) containing all the transactions.

9. state_root: Root hash of MPT that encodes entire state of the blockchain.

10. receipts_root: Root hash of MPT that contain receipts of all transactions in a block. A receipt basically consist of information about outcome of transactions.

11. miner: The address of the miner (or mining pool) that successfully mined the block. This is important for analyzing miner distribution and network decentralization.

12. difficulty: A number which represents the competition among miners to find the next block. This was previously used in ETH 1.0 before the transition to POS.

13. total_difficulty: A measure of the cumulative difficulty of all blocks up to and including this block. It reflects the competition among miners to find the next block.

14. size: The size of the block in bytes. This information is crucial for understanding how full the blocks are, indicating the load on the network.

15. extra_data: A field used by miners to include additional data to the block.

16. gas_limit: The maximum amount of gas allowed to be included in the block. This cap ensures that block processing times remain stable.

17. gas_used: The total amount of gas used by all transactions in the block. It shows how much of the block’s gas limit was utilized.

18. transaction_count: The number of transactions included in the block. This can provide insights into the network's usage and throughput.

19. base_fee_per_gas: The minimum amount of fees to be paid to consider your transaction as valid.

Examples

WITH DailyStats AS (
    SELECT 
        date,
        SUM(gas_used) AS total_gas_used,
        AVG(difficulty) AS average_difficulty,
        COUNT(DISTINCT miner) AS unique_miners
    FROM 
        ethereum_blocks
    GROUP BY 
        date
),
MaxGasBlocks AS (
    SELECT 
        date,
        MAX(gas_used) AS max_gas_used
    FROM 
        ethereum_blocks
    GROUP BY 
        date
),
DailyMaxGas AS (
    SELECT 
        eb.date,
        eb.number AS block_number,
        eb.miner,
        eb.gas_used
    FROM 
        ethereum_blocks eb
    JOIN MaxGasBlocks mgb ON eb.date = mgb.date AND eb.gas_used = mgb.max_gas_used
)
SELECT 
    ds.date,
    ds.total_gas_used,
    ds.average_difficulty,
    ds.unique_miners,
    dmg.block_number,
    dmg.miner AS max_gas_miner,
    dmg.gas_used AS max_gas_used
FROM 
    DailyStats ds
JOIN 
    DailyMaxGas dmg ON ds.date = dmg.date
ORDER BY 
    ds.date DESC;