blocks as (

select

block_timestamp,

block_number,

lag(block_number) over (order by block_timestamp) as prev_block_height,

block_number - lag(block_number) over (order by block_timestamp) as block_height_diff

from ethereum.core.fact_blocks

where block_timestamp >= sysdate() - interval '6 hours'

),

blocks_metrics as (

-- gross metric over the 6 hour period

select

time_slice(block_timestamp, 15, 'MINUTE') as block_timestamp_15_min,

min(block_timestamp) as min_block_timestamp,

max(block_timestamp) as max_block_timestamp,

min(block_number) as min_block_height,

max(block_number) as max_block_height,

max(block_number) - min(block_number) as blocks_count,

sum(iff(block_height_diff > 1, block_height_diff - 1, 0)) as missed_blocks_count,

missed_blocks_count / (max_block_height - min_block_height) as missed_blocks_ratio

from blocks

group by 1

select

concat((row_number() over (order by block_timestamp_15_min desc) - 1) * 15, ' minutes behind chainhead') as label,

*

from blocks_metrics

order by 2 desc