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 )

sh-mhd2536