polygon1 as (select BLOCK_NUMBER, datediff(second,lag(BLOCK_TIMESTAMP,1) over( order by BLOCK_TIMESTAMP),BLOCK_TIMESTAMP) as diff from polygon.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25')

,polygon as ( select min(diff) as min_Time ,max(diff) as max_Time, avg(diff) as avg_Time from polygon1)

,solana1 as (select BLOCK_ID, datediff(second,lag(BLOCK_TIMESTAMP,1) over( order by BLOCK_TIMESTAMP),BLOCK_TIMESTAMP) as diff from solana.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25')

,solana as ( select min(diff) as min_Time ,max(diff) as max_Time, avg(diff) as avg_Time from solana1)

,flow1 as (select ID, datediff(second,lag(BLOCK_TIMESTAMP,1) over( order by BLOCK_TIMESTAMP),BLOCK_TIMESTAMP) as diff from flow.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25')

,flow as (select min(diff) as min_Time ,max(diff) as max_Time, avg(diff) as avg_Time from flow1)

,arbitrum1 as (select BLOCK_NUMBER, datediff(second,lag(BLOCK_TIMESTAMP,1) over( order by BLOCK_TIMESTAMP),BLOCK_TIMESTAMP) as diff from arbitrum.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25')

,arbitrum as (select min(diff) as min_Time ,max(diff) as max_Time, avg(diff) as avg_Time from arbitrum1)

,optimism1 as (select BLOCK_NUMBER, datediff(second,lag(BLOCK_TIMESTAMP,1) over( order by BLOCK_TIMESTAMP),BLOCK_TIMESTAMP) as diff from optimism.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25')

,optimism as (select min(diff) as min_Time ,max(diff) as max_Time, avg(diff) as avg_Time from optimism1)

select 'Polygon' as Name , *, (select round(avg(tx_count),0) from polygon.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25') as avg_txs from polygon

union all

select 'Solana' as Name , * , (select round(avg(tx_count),0) from solana.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25') as num_avg_txs from solana

union all

select 'Flow' as Name , * , (select round(avg(tx_count),0) from flow.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25') as num_avg_txs from flow

union all

select 'Arbitrum' as Name , * , (select round(avg(tx_count),0) from arbitrum.core.fact_blocks where BLOCK_TIMESTAMP::date<='2022-07-25') as num_avg_txs from arbitrum