With t1 AS(

SELECT BLOCK_NUMBER AS blocknum, BLOCK_TIMESTAMP AS timestamp, Rank() OVER (ORDER BY Block_Timestamp) AS index1

FROM polygon.core.fact_blocks

),

t2 AS(

SELECT BLOCK_NUMBER AS blocknum_previous, BLOCK_TIMESTAMP as timestamp_previous, Rank() OVER (ORDER BY Block_Timestamp)+1 AS index2

FROM polygon.core.fact_blocks

),

t3 AS (

SELECT blocknum,timestamp,blocknum_previous,timestamp_previous,DATEDIFF(s,timestamp_previous,timestamp) AS DIFF

FROM t1

JOIN t2 ON t1.index1 = t2.index2

),

t4 AS (

SELECT blocknum,timestamp,blocknum_previous,timestamp_previous,DIFF

FROM t3

WHERE blocknum = BLOCKNUM_PREVIOUS + 1

Select

(Select Max(DIFF) FROM t4) AS Max_diff ,

(Select Min(DIFF) FROM t4) AS Min_diff,

(Select AVG(DIFF) FROM t4) AS avg_diff,

(Select AVG(TX_COUNT) FROM polygon.core.fact_blocks) AS AVG_TX,

(Select MAX(TX_COUNT) FROM polygon.core.fact_blocks) AS Max_TX,

AVG_TX/avg_diff AS AVG_TPS

,(Select COUNT(*) FROM t4) AS total_blocks

),

FLOW AS (

With t1 AS(

SELECT BLOCK_HEIGHT AS blocknum, BLOCK_TIMESTAMP AS timestamp, Rank() OVER (ORDER BY Block_Timestamp) AS index1