-- Credited #M-Zamani

with

final as (

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

from aptos.core.fact_blocks a, aptos.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,'Sei' as chain, avg(datediff(second,a.block_timestamp, b.block_timestamp)) as avg_time

from sei.core.fact_blocks a, sei.core.fact_blocks b

where a.block_id = b.block_id-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,'Solana' as chain, avg(datediff(second,a.block_timestamp, b.block_timestamp)) as avg_time

from solana.core.fact_blocks a, solana.core.fact_blocks b

where a.block_id = b.block_id-1

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

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

group by 1,2

select

date,

chain ,

avg(avg_time) as "Average Time"

from final a join ethereum.core.dim_dates b on a.date = b.date_day

where date >= current_date() - 30 and date < current_date

group by 1,2