WITH SOL AS ( SELECT avg(datediff(second , b1.block_timestamp::timestamp , b2.block_timestamp::timestamp)) as avg_time, min(datediff(second , b1.block_timestamp::timestamp , b2.block_timestamp::timestamp)) as min_time, max(datediff(second , b1.block_timestamp::timestamp , b2.block_timestamp::timestamp)) as max_time, avg(b1.tx_count) as avg_tx_count, 'Solana' as network From solana.core.fact_blocks b1 ,solana.core.fact_blocks b2 WHERE b2.previous_block_id = b1.block_id and b1.block_timestamp <= CURRENT_DATE - 30 and b2.block_timestamp <= CURRENT_DATE - 30 and b2.block_timestamp > b1.block_timestamp ), POLY AS ( SELECT avg(datediff(second , p1.block_timestamp::timestamp , p2.block_timestamp::timestamp)) as avg_time, min(datediff(second , p1.block_timestamp::timestamp , p2.block_timestamp::timestamp)) as min_time, max(datediff(second , p1.block_timestamp::timestamp , p2.block_timestamp::timestamp)) as max_time, avg(p1.tx_count) as avg_tx_count, 'Polygon' as network From polygon.core.fact_blocks p1 ,polygon.core.fact_blocks p2 WHERE p2.block_number-1 = p1.block_number and p1.block_timestamp <= CURRENT_DATE - 30 and p2.block_timestamp <= CURRENT_DATE - 30 and p2.block_timestamp > p1.block_timestamp ), ETH AS ( SELECT avg(datediff(second , e1.block_timestamp::timestamp , e2.block_timestamp::timestamp)) as avg_time, min(datediff(second , e1.block_timestamp::timestamp , e2.block_timestamp::timestamp)) as min_time, max(datediff(second , e1.block_timestamp::timestamp , e2.block_timestamp::timestamp)) as max_time, avg(e1.tx_count) as avg_tx_count, 'Ethereum' as network From ethereum.core.fact_blocks e1 ,ethereum.core.fact_blocks e2 WHERE e2.block_number-1 = e1.block_number and e1.block_timestamp <= CURRENT_DATE - 30 and e2.block_timestamp <= CURRENT_DATE - 30 and e2.block_timestamp > e1.block_timestamp ), FLOW AS ( SELECT avg(datediff(second , f1.block_timestamp::timestamp , f2.block_timestamp::timestamp)) as avg_time, min(datediff(second , f1.block_timestamp::timestamp , f2.block_timestamp::timestamp)) as min_time, max(datediff(second , f1.block_timestamp::timestamp , f2.block_timestamp::timestamp)) as max_time, avg(f1.tx_count) as avg_tx_count, 'Flow' as network From flow.core.fact_blocks f1 ,flow.core.fact_blocks f2 WHERE f2.block_height-1 = f1.block_height and f1.block_timestamp <= CURRENT_DATE - 30 and f2.block_timestamp <= CURRENT_DATE - 30 and f2.block_timestamp > f1.block_timestamp ), OSMO AS ( SELECT avg(datediff(second , o1.block_timestamp::timestamp , o2.block_timestamp::timestamp)) as avg_time, min(datediff(second , o1.block_timestamp::timestamp , o2.block_timestamp::timestamp)) as min_time, max(datediff(second , o1.block_timestamp::timestamp , o2.block_timestamp::timestamp)) as max_time, avg(o1.tx_count) as avg_tx_count, 'Osmosis' as network From osmosis.core.fact_blocks o1 ,osmosis.core.fact_blocks o2 WHERE o2.block_id-1 = o1.block_id and o1.block_timestamp <= CURRENT_DATE - 30 and o2.block_timestamp <= CURRENT_DATE - 30 and o2.block_timestamp > o1.block_timestamp ), GNO AS ( SELECT avg(datediff(second , g1.block_timestamp::timestamp , g2.block_timestamp::timestamp)) as avg_time, min(datediff(second , g1.block_timestamp::timestamp , g2.block_timestamp::timestamp)) as min_time, max(datediff(second , g1.block_timestamp::timestamp , g2.block_timestamp::timestamp)) as max_time, avg(g1.tx_count) as avg_tx_count, 'Gnosis' as network From gnosis.core.fact_blocks g1 ,gnosis.core.fact_blocks g2 WHERE g2.block_number-1 = g1.block_number and g1.block_timestamp <= CURRENT_DATE - 30 and g2.block_timestamp <= CURRENT_DATE - 30 and g2.block_timestamp > g1.block_timestamp ), NEAR AS ( SELECT avg(datediff(second , n1.block_timestamp::timestamp , n2.block_timestamp::timestamp)) as avg_time, min(datediff(second , n1.block_timestamp::timestamp , n2.block_timestamp::timestamp)) as min_time, max(datediff(second , n1.block_timestamp::timestamp , n2.block_timestamp::timestamp)) as max_time, avg(n1.tx_count) as avg_tx_count, 'Near' as network From near.core.fact_blocks n1 ,near.core.fact_blocks n2 WHERE n2.block_id-1 = n1.block_id and n1.block_timestamp <= CURRENT_DATE - 30 and n2.block_timestamp <= CURRENT_DATE - 30 and n2.block_timestamp > n1.block_timestamp ), BSC AS ( SELECT avg(datediff(second , b1.block_timestamp::timestamp , b2.block_timestamp::timestamp)) as avg_time, min(datediff(second , b1.block_timestamp::timestamp , b2.block_timestamp::timestamp)) as min_time, max(datediff(second , b1.block_timestamp::timestamp , b2.block_timestamp::timestamp)) as max_time, avg(b1.tx_count) as avg_tx_count, 'BSC' as network From bsc.core.fact_blocks b1 ,bsc.core.fact_blocks b2 WHERE b2.block_number-1 = b1.block_number and b1.block_timestamp <= CURRENT_DATE - 30 and b2.block_timestamp <= CURRENT_DATE - 30 and b2.block_timestamp > b1.block_timestamp ), AVA AS ( SELECT avg(datediff(second , a1.block_timestamp::timestamp , a2.block_timestamp::timestamp)) as avg_time, min(datediff(second , a1.block_timestamp::timestamp , a2.block_timestamp::timestamp)) as min_time, max(datediff(second , a1.block_timestamp::timestamp , a2.block_timestamp::timestamp)) as max_time, avg(a1.tx_count) as avg_tx_count, 'Avalanche' as network From avalanche.core.fact_blocks a1 ,avalanche.core.fact_blocks a2 WHERE a2.block_number-1 = a1.block_number and a1.block_timestamp <= CURRENT_DATE - 30 and a2.block_timestamp <= CURRENT_DATE - 30 and a2.block_timestamp > a1.block_timestamp ) SELECT * FROM SOL UNION ALL SELECT * FROM POLY UNION ALL SELECT * FROM ETH UNION ALL SELECT * FROM FLOW UNION ALL SELECT * FROM OSMO UNION ALL SELECT * FROM GNO UNION ALL SELECT * FROM NEAR UNION ALL SELECT * FROM BSC UNION ALL SELECT * FROM AVA

mirektheprinter