flipside

    07/22 Polygon Block performance: overall stats III :time_diff_distro
    elvis07/22 Polygon Block performance: overall stats III :time_diff_distro
    Updated 2023-04-26
    /*What is the average time between blocks on Polygon?
    What was the maximum and minimum recorded time between two blocks? How many transactions are done in a block on average?
    How do these numbers compare to L1 such as Flow or Solana, or other L2 such as Arbitrum or Optimism? */
    WITH Polygon_block_data_improved AS (
    SELECT P.block_number, P.block_timestamp, P.tx_count, P.difficulty, P.network, P.gas_used, P.size, datediff('second',P.block_timestamp,C.block_timestamp) as timediff,
    P.block_number-C.block_number as block_number_diff
    FROM polygon.core.fact_blocks C LEFT JOIN polygon.core.fact_blocks P ON C.parent_hash = P.hash
    ),
    Polygon_overall_block_stats AS (
    SELECT min(timediff) as Min_block_diff, max(timediff) as Max_block_diff, avg(timediff) as Avg_block_diff, median(timediff) as Median_block_diff,
    min(tx_count) as Min_tx_count, max(tx_count) as Max_tx_count, avg(tx_count) as Avg_tx_count, median(tx_count) as Median_tx_count
    FROM Polygon_block_data_improved
    ),
    Polygon_overall_block_stats_barchartified_timediff AS (
    (SELECT 'Min' AS descriptor, Min_block_diff as Polygon, 1 as bar_order
    FROM Polygon_overall_block_stats) UNION
    (SELECT 'Max' AS descriptor, Max_block_diff as Polygon, 2 as bar_order
    FROM Polygon_overall_block_stats) UNION
    (SELECT 'Average' AS descriptor, Avg_block_diff as Polygon, 3 as bar_order
    FROM Polygon_overall_block_stats) UNION
    (SELECT 'Median' AS descriptor, Median_block_diff as Polygon, 4 as bar_order
    FROM Polygon_overall_block_stats)
    ),
    Polygon_overall_block_stats_barchartified_tx_count AS (
    (SELECT 'Min' AS descriptor, Min_tx_count as Polygon, 1 as bar_order
    FROM Polygon_overall_block_stats) UNION
    (SELECT 'Max' AS descriptor, Max_tx_count as Polygon, 2 as bar_order
    FROM Polygon_overall_block_stats) UNION
    (SELECT 'Average' AS descriptor, Avg_tx_count as Polygon, 3 as bar_order
    FROM Polygon_overall_block_stats) UNION
    (SELECT 'Median' AS descriptor, Median_tx_count as Polygon, 4 as bar_order
    FROM Polygon_overall_block_stats)
    ),
    block_timediff_ranges AS (
    SELECT key AS cat_name, value[0] AS cat_rng_str, value[1] AS cat_order, value[2] AS cat_range_bottom, value[3] AS cat_range_top
    FROM table(flatten(input => parse_json('{"Cat 1":["0-2 sec", 1, 0, 2],
    Run a query to Download Data