WITH
blocks AS (
SELECT
block_number
, block_timestamp
, tx_count
, lag(block_timestamp) OVER (order by block_timestamp) AS prev_block_timestamp
, datediff('second', prev_block_timestamp, block_timestamp) AS seconds_since_prev_block
FROM avalanche.core.fact_blocks
WHERE block_timestamp >= '2022-06-20 00:00:00'
)
SELECT
count(*) AS total_blocks
, avg(tx_count) AS avg_transactions_per_block
, max(tx_count) AS max_transactions_per_block
, min(tx_count) AS min_transactions_per_block
, avg(seconds_since_prev_block) AS avg_seconds_since_prev_block
FROM blocks
LIMIT 100