date_trunc('{{granularity}}', date) as dates,

round(avg(avg_time_diff),4) as avg_block_time

SELECT

date_trunc('hour',a.block_timestamp) as date,

avg(datediff(second, a.block_timestamp,b.block_timestamp)) as avg_time_diff

FROM aptos.core.fact_blocks a, aptos.core.fact_blocks b

WHERE a.block_number= b.block_number-1

AND date <= CURRENT_DATE-1

AND date > '2020-12-29 00:00:00.000'

GROUP BY date

GROUP BY 1

ORDER BY 1 DESC