-- Credited #M-Zamani

with final as (

select

date(a.block_timestamp) as date,

'Arbitrum' as chain,

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

from arbitrum.core.fact_blocks a, arbitrum.core.fact_blocks b

where a.block_number = b.block_number-1

and a.block_timestamp::date >= '2023-08-15'

and b.block_timestamp::date >= '2023-08-15'

group by 1,2

UNION ALL

select date(a.block_timestamp) as date,'Optimism' as chain, avg(datediff(second,a.block_timestamp, b.block_timestamp)) as avg_time

from optimism.core.fact_blocks a, optimism.core.fact_blocks b

where a.block_number = b.block_number-1

and a.block_timestamp::date >= '2023-08-15'

and b.block_timestamp::date >= '2023-08-15'

group by 1,2

UNION ALL

select date(a.block_timestamp) as date,'Ethereum' as chain, avg(datediff(second,a.block_timestamp, b.block_timestamp)) as avg_time

from ethereum.core.fact_blocks a, ethereum.core.fact_blocks b

where a.block_number = b.block_number-1

and a.block_timestamp::date >= '2023-08-15'

and b.block_timestamp::date >= '2023-08-15'

group by 1,2

UNION ALL

select date(a.block_timestamp) as date,'Polygon' as chain, avg(datediff(second,a.block_timestamp, b.block_timestamp)) as avg_time

from polygon.core.fact_blocks a, polygon.core.fact_blocks b

where a.block_number = b.block_number-1

and a.block_timestamp::date >= '2023-08-15'

and b.block_timestamp::date >= '2023-08-15'

group by 1,2

UNION ALL

select date(a.block_timestamp) as date,'BSC' as chain, avg(datediff(second,a.block_timestamp, b.block_timestamp)) as avg_time