flipsidecryptoBlocks, Arbitrum, History
    Updated 2023-12-13
    with test_data as (
    select
    *,
    row_number() over (partition by date(test_timestamp) order by test_timestamp desc) as rn,
    count(*) over (partition by date(test_timestamp)) as tests_per_day,
    min(min_block) over (partition by date(test_timestamp)) as day_min_block,
    max(max_block) over (partition by date(test_timestamp)) as day_max_block
    from bi_analytics.core.fact_data_observability_results
    where
    blockchain = 'arbitrum' and test_name = 'blocks'
    ),

    last_tests as (select * from test_data where rn = 1),

    blocks_status as (
    select
    date(test_timestamp) as date,
    sum(case when rn = 1 and blocks_impacted_count > 0 then blocks_impacted_count else 0 end) as blocks_still_impacted,
    sum(case when rn < tests_per_day and blocks_impacted_count > 0 then blocks_impacted_count else 0 end) as blocks_fixed,
    day_min_block,
    day_max_block,
    day_max_block - day_min_block + 1 as total_blocks
    from test_data
    group by
    date(test_timestamp),
    day_min_block,
    day_max_block
    )

    select
    date,
    day_min_block,
    day_max_block,
    total_blocks,
    blocks_still_impacted,
    blocks_fixed,
    Run a query to Download Data