SELECT

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 gnosis.core.fact_blocks a, gnosis.core.fact_blocks b

WHERE a.block_number= b.block_number-1

AND date <= CURRENT_DATE-1

GROUP BY date

GROUP BY 1

ORDER BY 1 DESC