msafadoostblocks in flow
    Updated 2022-07-25
    WITH total AS (
    SELECT BLOCK_TIMESTAMP as sec1,
    BLOCK_HEIGHT as BLOCK_HEIGHT1,
    row_number()over(ORDER by BLOCK_HEIGHT1) as rank
    FROM flow.core.fact_blocks
    GROUP by 1,2
    ),
    limited AS (
    SELECT BLOCK_TIMESTAMP as sec2,
    BLOCK_HEIGHT as BLOCK_HEIGHT2,
    row_number()over(ORDER by BLOCK_HEIGHT2) as rank
    FROM flow.core.fact_blocks
    WHERE BLOCK_HEIGHT != (SELECT min(BLOCK_HEIGHT) FROM flow.core.fact_blocks)
    GROUP by 1,2
    ),
    block_times AS (
    SELECT BLOCK_HEIGHT1,
    CASE
    WHEN BLOCK_HEIGHT2-BLOCK_HEIGHT1 = 1 AND sec2>sec1 THEN cast(DATEDIFF(SECOND, sec1, sec2) as INT)
    END AS secs
    FROM total JOIN limited USING(rank)
    GROUP by 1,2 HAVING(secs is NOT NULL)
    ORDER by 2 ASC
    ),
    total_block as (
    SELECT
    avg(secs) as avg_time,
    min(secs) as min_time,
    max(secs) as max_time
    FROM block_times
    ),
    txes AS
    (
    SELECT avg(TX_COUNT) as avg_tx,
    max(TX_COUNT) as max_tx,
    min(TX_COUNT) as min_tx
    Run a query to Download Data