flipside

    Monitoring - Blocks Gaps
    forgashMonitoring - Blocks Gaps
    Updated 2023-05-26
    -- individual missing blocks
    WITH source AS (
    SELECT
    {{block_num_column}},
    LAG(
    {{block_num_column}},
    1
    ) over (
    ORDER BY
    {{block_num_column}} ASC
    ) AS prev_block_height
    FROM
    (select distinct {{block_num_column}} from
    {{blockchain}}.core.fact_blocks
    )
    )
    SELECT
    date_trunc('d', b.block_timestamp) as _date,
    -- a.block_height,
    -- a.prev_block_height,
    sum(a.{{block_num_column}} - a.prev_block_height - 1) AS gap
    FROM
    source a
    left join {{blockchain}}.core.fact_blocks b using ({{block_num_column}})
    WHERE
    a.{{block_num_column}} - a.prev_block_height <> 1
    and block_timestamp > current_date - interval '{{date_interval}}'
    GROUP BY 1

    Run a query to Download Data