mlhUntitled Query
    Updated 2022-07-23
    WITH block1 AS (
    SELECT
    BLOCK_TIMESTAMP AS "date1",
    BLOCK_NUMBER AS "block1"
    FROM polygon.core.fact_blocks
    WHERE "date1" >= CURRENT_DATE - 7
    ),
    block2 AS (
    SELECT
    BLOCK_TIMESTAMP AS "date2",
    BLOCK_NUMBER AS "block2"
    FROM polygon.core.fact_blocks
    WHERE "date2" >= CURRENT_DATE - 30
    ),
    interval AS (
    SELECT
    DATEDIFF ('second', "date1" , "date2") AS "Time between blocks"
    FROM block1, block2
    WHERE "block2" - "block1" = 1
    )
    SELECT
    min ("Time between blocks") AS "Minimum time between two blocks",
    max ("Time between blocks") AS "Maximum time between two blocks",
    sum ("Time between blocks")/COUNT (*) AS "Average time between two blocks"
    FROM interval