flipside

    Ethereum Average block times copy
    zackmendelEthereum Average block times copy
    Updated 2025-04-01
    WITH ethereum_base AS (
    SELECT
    block_number,
    block_timestamp,
    LAG(block_timestamp) OVER (ORDER BY block_number) AS previous_timestamp,
    DATEDIFF('second', previous_timestamp, block_timestamp) AS block_time
    FROM ethereum.core.fact_blocks
    --WHERE block_timestamp::date >= CURRENT_DATE - 30 AND block_timestamp::date != CURRENT_DATE
    WHERE block_timestamp::date >= '2020-10-10' AND block_timestamp::date != CURRENT_DATE
    -- limit 1000
    )

    SELECT
    DATE_TRUNC(week, block_timestamp) AS timespan,
    COUNT(DISTINCT block_number) AS blocks,
    AVG(block_time) AS avg_block_time
    FROM ethereum_base
    GROUP BY 1
    ORDER BY 1


    Last run: 2 months ago
    TIMESPAN
    BLOCKS
    AVG_BLOCK_TIME
    1
    		2020-10-05 00:00:00.0001307813.208993
    2
    		2020-10-12 00:00:00.0004560713.261692
    3
    		2020-10-19 00:00:00.0004568513.238393
    4
    		2020-10-26 00:00:00.0004561613.25798
    5
    		2020-11-02 00:00:00.0004561013.260535
    6
    		2020-11-09 00:00:00.0004562113.257009
    7
    		2020-11-16 00:00:00.0004559313.265545
    8
    		2020-11-23 00:00:00.0004554813.278212
    9
    		2020-11-30 00:00:00.0004559113.265666
    10
    		2020-12-07 00:00:00.0004544113.309698
    11
    		2020-12-14 00:00:00.0004563513.252241
    12
    		2020-12-21 00:00:00.0004559913.264216
    13
    		2020-12-28 00:00:00.0004567513.24092
    14
    		2021-01-04 00:00:00.0004559313.265414
    15
    		2021-01-11 00:00:00.0004563313.253764
    16
    		2021-01-18 00:00:00.0004558813.266759
    17
    		2021-01-25 00:00:00.0004548413.296918
    18
    		2021-02-01 00:00:00.0004550313.291036
    19
    		2021-02-08 00:00:00.0004550413.291535
    20
    		2021-02-15 00:00:00.0004553413.282382
    ...
    235
    10KB
    18s