Transactions

The Ethereum Transactions Table is Raw Dataset that track and display comprehensive details about transactions occurring on the Ethereum network. This table is crucial for analyzing how Ether and data are transferred between addresses, how smart contracts are interacted with, and the overall activity and health of the Ethereum blockchain. Our Raw ethereum_transactions dataset consist of the following columns with the corresponding data types:

Data Types

Columns	Data Types
hash	string
nonce	bigint
transaction_index	bigint
from_address	string
to_address	string
value	double
gas	bigint
gas_price	bigint
input	string
receipt_cumulative_gas_used	bigint
receipt_gas_used	bigint
receipt_contract_address	string
receipt_status	bigint
block_timestamp	timestamp
block_number	bigint
block_hash	string
max_fee_per_gas	bigint
max_priority_fee_per_gas	bigint
transaction_type	bigint
receipt_effective_gas_price	bigint

Key Descriptions of Columns in Ethereum Transactions Table

1. hash: A unique identifier for each transaction. It is used to track and reference specific transactions within the blockchain.

2. nonce: A scalar value equal to the number of confirmed transactions sent from the sender's address. It's used to prevent transaction replay attacks.

3. transaction_index: The relative position of a specific transaction in a block.

4. from_address: The Ethereum address that initiated the transaction. This is often the address of the sender or the contract caller.

5. to_address: The Ethereum address of the transaction's recipient. This can be a user's address or a smart contract address.

6. value: The amount of Ether transferred in the transaction, measured in Wei (1 Ether = 1e18 Wei).

7. gas: Maximum amount of gas allocated for the transaction to be included in a block.

8. gas_price: The price per unit of gas specified in the transaction, typically measured in Gwei (1 Gwei = 1e9 Wei). Post EIP-1559, this includes the base fee per gas and a priority fee.

9. input: Any data sent along with the transaction, which is typically used when the transaction is interacting with a smart contract.

10. receipt_cumulative_gas_used: This field represents the total amount of gas used in the block containing the transaction up to and including that transaction.

11. receipt_gas_used: The total amount of gas that was consumed to execute the transaction. This is less than or equal to the gas limit provided by the sender.

12. receipt_contract_address: In transactions that involve a creation of smart contract, the address of the newly created contract will be available here.

13. receipt_status: Indicates whether the transaction was successful or failed, which helps in assessing the execution outcome of transactions. If the transaction is succesful, it is denoted by 1 and 0 if it fails.

14. block_timestamp: The date and time when the block containing the transaction was mined.

15. block_number: The number of the block in which the transaction was included. This links the transaction to a specific place in the Ethereum blockchain.

16. block_hash: A unique identifier for each block. It is used to track and reference specific blocks.

17. max_fee_per_gas: The maximum fees users is willing to pay for the transcation. (Denoted in WEI).

18. max_priority_fee_per_gas: The maximum fees user is willing to pay to the block producer. (Denoted in WEI).

19. transaction_type: Denotes if a transaction is Type 0 or Type 2. Type 0 is the legacy transactions and Type 2 is post london upgrade transactions.

20. receipt_effective_gas_price: Sum of gas_price and max_priority_fee_per_gas.

Examples

WITH DailyTransactions AS (
    SELECT 
        date,
        COUNT(*) AS total_transactions,
        SUM(value) AS total_value_transferred,
        AVG(CAST(gas_price AS DECIMAL(38,0)) * CAST(gas AS DECIMAL(38,0))) AS average_transaction_fee,
        COUNT(CASE WHEN receipt_status = 1 THEN 1 ELSE NULL END) AS successful_transactions,
        COUNT(CASE WHEN receipt_status = 0 THEN 1 ELSE NULL END) AS failed_transactions
    FROM 
        ethereum_transactions
    WHERE 
        date >= '2024-01-01' AND date <= '2024-12-31'
    GROUP BY 
        date
),
MaxValueTransactions AS (
    SELECT 
        date,
        MAX(value) AS max_transaction_value
    FROM 
        ethereum_transactions
    WHERE 
        date >= '2024-01-01' AND date <= '2024-12-31'
    GROUP BY 
        date
),
DailyMaxValue AS (
    SELECT 
        et.date,
        et.hash AS transaction_hash,
        et.from_address,
        et.to_address,
        et.value
    FROM 
        ethereum_transactions et
    JOIN MaxValueTransactions mvt ON et.date = mvt.date AND et.value = mvt.max_transaction_value
)
SELECT 
    dt.date,
    dt.total_transactions,
    dt.total_value_transferred,
    dt.average_transaction_fee,
    dt.successful_transactions,
    dt.failed_transactions,
    dmv.transaction_hash,
    dmv.from_address,
    dmv.to_address,
    dmv.value AS max_value_transaction
FROM 
    DailyTransactions dt
JOIN 
    DailyMaxValue dmv ON dt.date = dmv.date
ORDER BY 
    dt.date DESC;