SG data2024-06-25 10:51 AM
    Updated 2024-06-25
    WITH block_counts AS (
    SELECT
    BLOCK_NUMBER,
    COUNT(TX_HASH) AS transaction_count,
    MAX(BLOCK_TIMESTAMP) AS block_timestamp
    FROM
    ethereum.core.fact_transactions
    GROUP BY
    BLOCK_NUMBER
    order by block_number
    ),
    block_intervals AS (
    SELECT
    BLOCK_NUMBER,
    transaction_count,
    block_timestamp,
    LAG(block_timestamp) OVER (ORDER BY BLOCK_NUMBER) AS prev_block_timestamp
    FROM
    block_counts
    ),
    tps_calculations AS (
    SELECT
    BLOCK_NUMBER,
    transaction_count,
    block_timestamp,
    prev_block_timestamp,
    DATEDIFF('second', prev_block_timestamp, block_timestamp) AS time_diff,
    CASE
    WHEN prev_block_timestamp IS NOT NULL THEN transaction_count / time_diff
    ELSE NULL
    END AS tps
    FROM
    block_intervals
    )
    SELECT
    BLOCK_NUMBER,
    QueryRunArchived: QueryRun has been archived