misaghlbTerradash Part 1: Activity - blocks
Updated 2023-04-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
with succesful_blocks as (
SELECT DISTINCT BLOCK_ID
from terra.core.fact_transactions
where TX_SUCCEEDED = 'TRUE'
)
SELECT
case
when Time_Between_blocks_Second <= 2 then 'Less than 2 Sec'
when Time_Between_blocks_Second BETWEEN 2 and 3 then '2 - 3 Sec'
when Time_Between_blocks_Second BETWEEN 4 and 5 then '4 - 5 Sec'
when Time_Between_blocks_Second BETWEEN 6 and 10 then '6 - 10 Sec'
when Time_Between_blocks_Second > 10 then 'More than 10 sec'
end as status,
COUNT(*) as "Blocks Count"
FROM (
SELECT TX.BLOCK_TIMESTAMP, TX.TX_COUNT, DATEDIFF(SECOND, TXs.BLOCK_TIMESTAMP, TX.BLOCK_TIMESTAMP) AS Time_Between_blocks_Second
FROM terra.core.fact_blocks TX
JOIN terra.core.fact_blocks TXs
ON TX.BLOCK_ID = (TXs.BLOCK_ID + 1)
where TX.BLOCK_ID in (SELECT BLOCK_ID from succesful_blocks)
)
GROUP by status