mrwildcatflow: average time between blocks by hour
    Updated 2022-07-26

    with datos as
    (SELECT block_height,
    date_trunc('hour', block_timestamp) as blocks_hour,
    TIMEDIFF(second, lag(block_timestamp, 1) OVER (ORDER BY block_timestamp), block_timestamp) as diff_seconds
    FROM flow.core.fact_blocks
    WHERE block_timestamp > CURRENT_DATE - INTERVAL '30 DAY'
    AND network = 'mainnet'
    ORDER BY 1)

    SELECT blocks_hour, AVG(diff_seconds)
    FROM datos
    GROUP BY 1
    order by 1

    Run a query to Download Data