elvis07/22 Polygon Block performance: overall stats II :tx_count
Updated 2023-04-26
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
⌄
/*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 C.block_number, C.block_timestamp, C.tx_count, C.difficulty, C.network, C.gas_used, C.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)
)
SELECT *
Run a query to Download Data