WITH
raw as (
SELECT
BLOCK_NUMBER,
BLOCK_TIMESTAMP as BLOCK_TIMESTAMP,
LAG(BLOCK_TIMESTAMP, 1) over (order by BLOCK_TIMESTAMP) as LAG_BLOCK_TIMESTAMP,
TX_COUNT as TX_COUNT
FROM arbitrum.core.fact_blocks
WHERE BLOCK_TIMESTAMP >= '{{StartDate}}'
ORDER BY BLOCK_TIMESTAMP
)
SELECT
BLOCK_TIMESTAMP::date as date,
MIN(DIFF) AS "Minimum Difference",
MAX(DIFF) AS "Maximum Difference",
AVG(DIFF) AS "Average Difference"
FROM (
SELECT
BLOCK_TIMESTAMP,
TIMEDIFF('seconds', LAG_BLOCK_TIMESTAMP, BLOCK_TIMESTAMP) as DIFF
FROM raw
WHERE DIFF is not NULL
)
GROUP BY DATE
ORDER by DATE