SELECT *, AVG(time_between_blocks) OVER (PARTITION BY network ORDER BY date) as avg_time_between_blocks FROM ( SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Ethereum' as network FROM ( SELECT tt1.block_number as block_number, LAG(tt1.ts, 1) OVER(ORDER BY block_number) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY block_number), tt1.ts) AS diff_seconds FROM ( SELECT block_number, max(block_timestamp) AS ts FROM ethereum.core.fact_blocks GROUP BY block_number ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Polygon' as network FROM ( SELECT tt1.block_number as block_number, LAG(tt1.ts, 1) OVER(ORDER BY block_number) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY block_number), tt1.ts) AS diff_seconds FROM ( SELECT block_number, max(block_timestamp) AS ts FROM polygon.core.fact_blocks GROUP BY block_number ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Avalanche' as network FROM ( SELECT tt1.block_number as block_number, LAG(tt1.ts, 1) OVER(ORDER BY block_number) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY block_number), tt1.ts) AS diff_seconds FROM ( SELECT block_number, max(block_timestamp) AS ts FROM avalanche.core.fact_blocks GROUP BY block_number ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Binance (BSC)' as network FROM ( SELECT tt1.block_number as block_number, LAG(tt1.ts, 1) OVER(ORDER BY block_number) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY block_number), tt1.ts) AS diff_seconds FROM ( SELECT block_number, max(block_timestamp) AS ts FROM bsc.core.fact_blocks GROUP BY block_number ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Gnosis (xDAI)' as network FROM ( SELECT tt1.block_number as block_number, LAG(tt1.ts, 1) OVER(ORDER BY block_number) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY block_number), tt1.ts) AS diff_seconds FROM ( SELECT block_number, max(block_timestamp) AS ts FROM gnosis.core.fact_blocks GROUP BY block_number ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Solana' as network FROM ( SELECT tt1.BLOCK_ID as BLOCK_ID, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_ID) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_ID), tt1.ts) AS diff_seconds FROM ( SELECT BLOCK_ID, max(block_timestamp) AS ts FROM solana.core.fact_blocks GROUP BY BLOCK_ID ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Optimism' as network FROM ( SELECT tt1.BLOCK_NUMBER as BLOCK_NUMBER, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_NUMBER) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_NUMBER), tt1.ts) AS diff_seconds FROM ( SELECT BLOCK_NUMBER, max(block_timestamp) AS ts FROM optimism.core.fact_blocks GROUP BY BLOCK_NUMBER ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Arbitrum One' as network FROM ( SELECT tt1.BLOCK_NUMBER as BLOCK_NUMBER, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_NUMBER) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_NUMBER), tt1.ts) AS diff_seconds FROM ( SELECT BLOCK_NUMBER, max(block_timestamp) AS ts FROM arbitrum.core.fact_blocks GROUP BY BLOCK_NUMBER ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Near' as network FROM ( SELECT tt1.BLOCK_ID as BLOCK_ID, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_ID) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_ID), tt1.ts) AS diff_seconds FROM ( SELECT BLOCK_ID, max(block_timestamp) AS ts FROM near.core.fact_blocks GROUP BY BLOCK_ID ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Harmony' as network FROM ( SELECT tt1.BLOCK_ID as BLOCK_ID, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_ID) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_ID), tt1.ts) AS diff_seconds FROM ( SELECT BLOCK_ID, max(block_timestamp) AS ts FROM flipside_prod_db.mdao_harmony.blocks GROUP BY BLOCK_ID ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date UNION DISTINCT SELECT date_trunc('day',end_time) as date, avg(diff_seconds) as time_between_blocks, 'Algorand' as network FROM ( SELECT tt1.BLOCK_ID as BLOCK_ID, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_ID) AS start_time, tt1.ts as end_time, TIMESTAMPDIFF(SECOND, LAG(tt1.ts, 1) OVER(ORDER BY BLOCK_ID), tt1.ts) AS diff_seconds FROM ( SELECT BLOCK_ID, max(block_timestamp) AS ts FROM flipside_prod_db.algorand.block GROUP BY BLOCK_ID ORDER BY ts ASC ) as tt1 WHERE DATE(tt1.ts) > CURRENT_DATE - 60 AND DATE(tt1.ts) < CURRENT_DATE ) GROUP BY date )

Query	Creator	Created
Untitled Query gnosis near bsc arbitrum ethereum	mahdish	Sep 14, 2022
Untitled Query sushi bsc arbitrum ethereum swaps	mahdish	Nov 26, 2022
Untitled Query bsc arbitrum ethereum optimism avalanche	mahdish	Nov 25, 2022

mahdish