with base as (

SELECT

block_timestamp

, block_number

, tx_count

, gas_used

, gas_limit

, lag(block_timestamp) OVER (order by block_number ASC) as p_block_timestamp

from monad.testnet.fact_blocks

WHERE 1=1

AND block_timestamp>='2025-02-19'

date_trunc('hour', block_timestamp) as dhour

, COUNT(*) as blocks

, AVG(DATEDIFF('seconds', p_block_timestamp, block_timestamp)) as block_time_avg

, (60*60)/ COUNT(*) as block_time

-- these are actually stupid to do

-- , MEDIAN(DATEDIFF('seconds', p_block_timestamp, block_timestamp)) as block_time_median

-- , MODE(DATEDIFF('seconds', p_block_timestamp, block_timestamp)) as block_time_mode

-- only include non-empty blocks for gas_util perc stats

, AVG(CASE WHEN tx_count!=0 THEN ROUND((gas_used/gas_limit)*100, 2) ELSE NULL END) as gas_utilization_perc

from base

GROUP BY 1

having block_time_avg!=block_time

ORDER BY dhour ASC