mehdimarjanPolygon vs. Osmosis Blocks Time Difference
Updated 2022-07-25
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
›
⌄
WITH polygon_blocks AS (
SELECT BLOCK_NUMBER + 1 AS BLOCK_NUMBER2, BLOCK_TIMESTAMP
FROM polygon.core.fact_blocks
),
polygon_results AS(
SELECT fb.BLOCK_NUMBER , timestampdiff('SECOND', polygon_blocks.BLOCK_TIMESTAMP, fb.BLOCK_TIMESTAMP) AS difference
FROM polygon.core.fact_blocks fb
JOIN polygon_blocks ON BLOCK_NUMBER2 = fb.BLOCK_NUMBER
WHERE fb.BLOCK_TIMESTAMP::DATE >= '2022-06-07'
),
osmosis_blocks AS (
SELECT BLOCK_ID + 1 AS BLOCK_NUMBER2, BLOCK_TIMESTAMP
FROM osmosis.core.fact_blocks
),
osmosis_results AS(
SELECT fb.BLOCK_ID , timestampdiff('SECOND', osmosis_blocks.BLOCK_TIMESTAMP, fb.BLOCK_TIMESTAMP) AS difference
FROM osmosis.core.fact_blocks fb
JOIN osmosis_blocks ON BLOCK_NUMBER2 = fb.BLOCK_ID
WHERE fb.BLOCK_TIMESTAMP::DATE >= '2022-06-07'
)
SELECT MAX(difference) AS "Maximum Time Difference(s)", MIN(difference) AS "Minimum Time Difference(s)",
ROUND(AVG(difference),3) AS "Average Time Difference(s)", 'Polygon' AS LABEL
FROM polygon_results
UNION
SELECT MAX(difference) AS "Maximum Time Difference(s)", MIN(difference) AS "Minimum Time Difference(s)",
ROUND(AVG(difference),3) AS "Average Time Difference(s)", 'Osmosis' AS LABEL
FROM osmosis_results
ORDER BY LABEL
Run a query to Download Data