select block_timestamp

, lag(block_timestamp,1) over(order by block_timestamp) as block_timestamp_lag

, datediff(SECOND, block_timestamp_lag, block_timestamp) as block_timediff

from polygon.core.fact_blocks

where block_timestamp::date >='2022-07-15'

order by block_timestamp

select date_trunc('day',block_timestamp) as date

, round(avg(block_timediff),3) as "average block time"

, round(max(block_timediff),3) as "max block time"

, round(min(block_timediff),3) as "min block time"

from list

group by 1