SELECT

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

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

CASE WHEN date BETWEEN '2022-09-30' AND '2022-12-31' THEN 'Q4_2022'

WHEN date BETWEEN '2023-01-01' AND '2023-04-01' THEN 'Q1_2023'

ELSE 'Q' END as quarter

FROM osmosis.core.fact_blocks a, osmosis.core.fact_blocks b

WHERE a.block_id = b.block_id -1

AND date <= CURRENT_DATE-1

AND datediff(second, a.block_timestamp,b.block_timestamp) >0 AND datediff(second, a.block_timestamp,b.block_timestamp) <100

GROUP BY date

ORDER by date DESC)

SELECT avg(avg_time_diff) as avg_block_time,

quarter

FROM block_time

GROUP by quarter