jp12[Polygon] Time between blocks - Block time Solana
Updated 2022-07-26Copy Reference Fork
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
›
⌄
WITH block_times as (
SELECT block_timestamp, BLOCK_ID
FROM solana.core.fact_blocks
WHERE block_timestamp > '2022-06-20'
)
, tx_per_timestamp as (
SELECT block_timestamp, COUNT(DISTINCT BLOCK_ID) as num_blocks
FROM block_times
GROUP BY 1
ORDER BY 1 ASC
)
, block_time_combined as (
SELECT block_timestamp, lead(block_timestamp) over (order by block_timestamp ASC) as next_block_time,
datediff('seconds', block_timestamp, next_block_time) as blocktime, num_blocks
FROM (
SELECT DISTINCT block_timestamp
FROM block_times
ORDER BY block_timestamp ASC
) b INNER JOIN tx_per_timestamp t USING (block_timestamp)
-- LIMIT 500
)
SELECT to_char(date_trunc('hour', block_timestamp), 'yyyy-mm-dd HH24:MI:SS') as hour, avg(blocktime / num_blocks) as avg_block_time
, min(blocktime / num_blocks) as min_block_time, max(blocktime / num_blocks) as max_block_time
FROM block_time_combined
GROUP BY 1
HAVING avg_block_time < 4
ORDER BY 1 ASC
Run a query to Download Data