misaghlbFLOW Speed (redux)
Updated 2022-12-13
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 succesful_blocks as (
SELECT DISTINCT BLOCK_ID
from terra.core.fact_transactions
where TX_SUCCEEDED = 'TRUE'
)
SELECT 'Flow' as type, date_trunc('week', BLOCK_TIMESTAMP) as block_date,
AVG(Time_Between_blocks_Second) AS "AVG Time Per block (Sec)",
MIN(Time_Between_blocks_Second) AS "MIN Time Per block (Sec)",
MAX(Time_Between_blocks_Second) AS "MAX Time Per block (Sec)",
COUNT(*) as "Blocks Count",
AVG(TX_COUNT) as "AVG TX Per Block",
MIN(TX_COUNT) as "MIN TX Per Block",
MAX(TX_COUNT) as "MAX TX Per Block"
FROM (
SELECT TX.BLOCK_TIMESTAMP, TX.TX_COUNT,
datediff (second,CURRENT_DATE,block_timestamp) - avg (datediff (second,CURRENT_DATE,block_timestamp)) over (order by block_timestamp rows 1 PRECEDING) as Time_Between_blocks_Second
FROM flow.core.fact_blocks TX
where TX.block_timestamp::date >= '2022-01-01'
)
GROUP by block_date
UNION
SELECT 'Ethereum' as type, date_trunc('week', BLOCK_TIMESTAMP) as block_date,
AVG(Time_Between_blocks_Second) AS "AVG Time Per block (Sec)",
MIN(Time_Between_blocks_Second) AS "MIN Time Per block (Sec)",
MAX(Time_Between_blocks_Second) AS "MAX Time Per block (Sec)",
COUNT(*) as "Blocks Count",
AVG(TX_COUNT) as "AVG TX Per Block",
MIN(TX_COUNT) as "MIN TX Per Block",
MAX(TX_COUNT) as "MAX TX Per Block"
FROM (
SELECT TX.BLOCK_TIMESTAMP, TX.TX_COUNT,
datediff (second,CURRENT_DATE,block_timestamp) - avg (datediff (second,CURRENT_DATE,block_timestamp)) over (order by block_timestamp rows 1 PRECEDING) as Time_Between_blocks_Second
FROM ethereum.core.fact_blocks TX
where TX.block_timestamp::date >= '2022-01-01'
Run a query to Download Data