mlhUntitled Query
Updated 2022-07-23
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
›
⌄
WITH block1 AS (
SELECT
BLOCK_TIMESTAMP AS "date1",
BLOCK_NUMBER AS "block1"
FROM polygon.core.fact_blocks
WHERE "date1" >= CURRENT_DATE - 7
),
block2 AS (
SELECT
BLOCK_TIMESTAMP AS "date2",
BLOCK_NUMBER AS "block2"
FROM polygon.core.fact_blocks
WHERE "date2" >= CURRENT_DATE - 30
),
interval AS (
SELECT
DATEDIFF ('second', "date1" , "date2") AS "Time between blocks"
FROM block1, block2
WHERE "block2" - "block1" = 1
)
SELECT
min ("Time between blocks") AS "Minimum time between two blocks",
max ("Time between blocks") AS "Maximum time between two blocks",
sum ("Time between blocks")/COUNT (*) AS "Average time between two blocks"
FROM interval