msafadoostblocks in flow
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 total AS (
SELECT BLOCK_TIMESTAMP as sec1,
BLOCK_HEIGHT as BLOCK_HEIGHT1,
row_number()over(ORDER by BLOCK_HEIGHT1) as rank
FROM flow.core.fact_blocks
GROUP by 1,2
),
limited AS (
SELECT BLOCK_TIMESTAMP as sec2,
BLOCK_HEIGHT as BLOCK_HEIGHT2,
row_number()over(ORDER by BLOCK_HEIGHT2) as rank
FROM flow.core.fact_blocks
WHERE BLOCK_HEIGHT != (SELECT min(BLOCK_HEIGHT) FROM flow.core.fact_blocks)
GROUP by 1,2
),
block_times AS (
SELECT BLOCK_HEIGHT1,
CASE
WHEN BLOCK_HEIGHT2-BLOCK_HEIGHT1 = 1 AND sec2>sec1 THEN cast(DATEDIFF(SECOND, sec1, sec2) as INT)
END AS secs
FROM total JOIN limited USING(rank)
GROUP by 1,2 HAVING(secs is NOT NULL)
ORDER by 2 ASC
),
total_block as (
SELECT
avg(secs) as avg_time,
min(secs) as min_time,
max(secs) as max_time
FROM block_times
),
txes AS
(
SELECT avg(TX_COUNT) as avg_tx,
max(TX_COUNT) as max_tx,
min(TX_COUNT) as min_tx
Run a query to Download Data