jp12[Polygon] Time between blocks - Block time Solana
    Updated 2022-07-26
    WITH block_times as (
    SELECT block_timestamp, BLOCK_ID
    FROM solana.core.fact_blocks
    WHERE block_timestamp > '2022-06-20'
    )
    , tx_per_timestamp as (
    SELECT block_timestamp, COUNT(DISTINCT BLOCK_ID) as num_blocks
    FROM block_times
    GROUP BY 1
    ORDER BY 1 ASC
    )

    , block_time_combined as (
    SELECT block_timestamp, lead(block_timestamp) over (order by block_timestamp ASC) as next_block_time,
    datediff('seconds', block_timestamp, next_block_time) as blocktime, num_blocks
    FROM (
    SELECT DISTINCT block_timestamp
    FROM block_times
    ORDER BY block_timestamp ASC
    ) b INNER JOIN tx_per_timestamp t USING (block_timestamp)
    -- LIMIT 500
    )

    SELECT to_char(date_trunc('hour', block_timestamp), 'yyyy-mm-dd HH24:MI:SS') as hour, avg(blocktime / num_blocks) as avg_block_time
    , min(blocktime / num_blocks) as min_block_time, max(blocktime / num_blocks) as max_block_time
    FROM block_time_combined
    GROUP BY 1
    HAVING avg_block_time < 4
    ORDER BY 1 ASC
    Run a query to Download Data