block_info AS

SELECT distinct block_timestamp, block_number FROM polygon.core.fact_transactions where block_timestamp BETWEEN '2022-06-22' and '2022-07-22' order by 2),

ibt as ( SELECT datediff('second', lag(block_timestamp, 1) over (order by block_number asc), block_timestamp) as interblock_time FROM block_info qualify interblock_time is not null)

SELECT avg(interblock_time) as avg_ibt, max(interblock_time) as max_ibt, min(interblock_time) as min_ibt, 'Polygon' as label FROM ibt ) UNION

(with block_info AS (SELECT distinct block_id, block_timestamp FROM solana.core.fact_transactions where block_timestamp BETWEEN '2022-06-22' and '2022-07-22' order by block_id),

ibt as ( SELECT datediff('second', lag(block_timestamp, 1) over (order by block_id asc), block_timestamp) as interblock_time FROM block_info qualify interblock_time is not null)

SELECT avg(interblock_time) as avg_ibt, max(interblock_time) as max_ibt, min(interblock_time) as min_ibt, 'Solana' as label FROM ibt ) UNION

(with block_info AS (SELECT distinct block_number, block_timestamp FROM ethereum.core.fact_transactions where block_timestamp BETWEEN '2022-06-22' and '2022-07-22' order by block_number),

ibt as ( SELECT datediff('second', lag(block_timestamp, 1) over (order by block_number asc), block_timestamp) as interblock_time FROM block_info qualify interblock_time is not null)

SELECT avg(interblock_time) as avg_ibt, max(interblock_time) as max_ibt, min(interblock_time) as min_ibt, 'Ethereum' as label FROM ibt ) UNION

(with block_info AS (SELECT distinct block_number, block_timestamp FROM optimism.core.fact_transactions where block_timestamp BETWEEN '2022-06-22' and '2022-07-22' order by block_number),

ibt as ( SELECT datediff('second', lag(block_timestamp, 1) over (order by block_number asc), block_timestamp) as interblock_time FROM block_info qualify interblock_time is not null)

SELECT avg(interblock_time) as avg_ibt, max(interblock_time) as max_ibt, min(interblock_time) as min_ibt, 'Optimism' as label FROM ibt ) UNION

(with block_info AS (SELECT distinct block_number, block_timestamp FROM arbitrum.core.fact_transactions where block_timestamp BETWEEN '2022-06-22' and '2022-07-22' order by block_number),

ibt as ( SELECT datediff('second', lag(block_timestamp, 1) over (order by block_number asc), block_timestamp) as interblock_time FROM block_info qualify interblock_time is not null)

SELECT avg(interblock_time) as avg_ibt, max(interblock_time) as max_ibt, min(interblock_time) as min_ibt, 'Arbitrum' as label FROM ibt )