with data as (select block_number, block_timestamp, tx_count,
lead(block_timestamp, 1, null) over (order by block_number desc) as prev_block_time,
abs(datediff('second', block_timestamp, prev_block_time)) as seconds_between_blocks
from avalanche.core.fact_blocks
where block_timestamp >= '2022-6-20')
select
date_trunc('hour', block_timestamp) as hour,
avg(seconds_between_blocks) as avg_tbb
from data
group by hour