SELECT 'Polygon' AS blockchain

, block_number AS block_height, block_timestamp, tx_count

FROM polygon.core.fact_blocks

WHERE block_timestamp >= '2022-06-25'),

blocks_with_tps AS (

SELECT *

, lag(block_timestamp) OVER (partition by blockchain

order by block_height) AS prev_block_timestamp

, datediff('millisecond', prev_block_timestamp, block_timestamp) / 1000.000 AS Time_Between_Blocks

, tx_count / nullif(Time_Between_Blocks, 0) AS tps

FROM blocks

SELECT blockchain

, date_trunc('hour', block_timestamp) AS time

, avg(Time_Between_Blocks) AS "Average Time Between Blocks"

, min(Time_Between_Blocks) AS "Minimum Time Between Blocks"

, max(Time_Between_Blocks) AS "Maximum Time Between Blocks"

, avg(tx_count) AS "Average Transactions Per Block"

FROM blocks_with_tps

WHERE blockchain = 'Polygon'

GROUP BY 1,2