SELECT DISTINCT BLOCK_ID

from terra.core.fact_transactions

where TX_SUCCEEDED = 'TRUE'

SELECT date_trunc('week', BLOCK_TIMESTAMP) as block_date,

AVG(Time_Between_blocks_Second) AS "AVG Time Per block (Sec)",

MIN(Time_Between_blocks_Second) AS "MIN Time Per block (Sec)",

MAX(Time_Between_blocks_Second) AS "MAX Time Per block (Sec)",

COUNT(*) as "Blocks Count",

AVG(TX_COUNT) as "AVG TX Per Block",

MIN(TX_COUNT) as "MIN TX Per Block",

MAX(TX_COUNT) as "MAX TX Per Block"

FROM (

SELECT TX.BLOCK_TIMESTAMP, TX.TX_COUNT, DATEDIFF(SECOND, TXs.BLOCK_TIMESTAMP, TX.BLOCK_TIMESTAMP) AS Time_Between_blocks_Second

FROM terra.core.fact_blocks TX

JOIN terra.core.fact_blocks TXs

ON TX.BLOCK_ID = (TXs.BLOCK_ID + 1)

where TX.BLOCK_ID in (SELECT BLOCK_ID from succesful_blocks)

GROUP by block_date