with tbl1 AS(

SELECT block_id, lag(block_timestamp,1) over (order by block_timestamp asc) as last_block_time , min(block_timestamp) AS block_time

FROM solana.core.fact_transactions

where block_id IN

(

SELECT DISTINCT block_id

FROM solana.core.fact_transactions

order by block_id

)

group by block_id, block_timestamp

)

SELECT

sum(datediff(second, last_block_time, block_time))/COUNT(datediff(second, last_block_time, block_time)) as avg

from tbl1

order by block_id