flipside

    Block Gaps
    robel91Block Gaps
    Updated 2023-01-31
    -- individual missing blocks
    WITH source AS (
    SELECT
    block_number,
    LAG(
    block_number,
    1
    ) over (
    ORDER BY
    block_number ASC
    ) AS prev_block_number
    FROM
    (
    SELECT
    DISTINCT block_number
    FROM
    optimism.core.fact_blocks
    )
    )
    SELECT
    DATE_TRUNC(
    'd',
    b.block_timestamp
    ) AS _date,
    SUM(
    A.block_number - A.prev_block_number - 1
    ) AS gap
    FROM
    source A
    LEFT JOIN optimism.core.fact_blocks b USING (block_number)
    WHERE
    A.block_number - A.prev_block_number <> 1
    AND block_timestamp >= to_date('2022-04-20') + INTERVAL '12 hours'
    GROUP BY
    1
    Run a query to Download Data