flipsidecryptoReceipts, Aurora, History
    Updated 2023-10-17
    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 = 'aurora' and test_name = 'receipts'
    ),

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

    blocks_status as (
    select
    test_timestamp,
    blocks_impacted_count,
    rn,
    case when rn = 1 and blocks_impacted_count > 0 then blocks_impacted_count else 0 end as n_impacted,
    case when lag(blocks_impacted_count, 1) over (order by test_timestamp ASC) - blocks_impacted_count > 0
    THEN lag(blocks_impacted_count, 1) over (order by test_timestamp ASC) - blocks_impacted_count
    ELSE 0 END AS n_fixed,
    day_min_block,
    day_max_block,
    day_max_block - day_min_block + 1 as total_blocks
    from test_data
    )
    --select * from blocks_status;
    select
    date(test_timestamp) AS date,
    day_min_block,
    day_max_block,
    total_blocks,
    sum(n_impacted) AS blocks_still_impacted,
    sum(n_fixed) AS blocks_fixed,
    Run a query to Download Data