WITH block_data AS (

SELECT

date_trunc({{period_type}}, block_timestamp) AS period_start,

block_timestamp,

COUNT(DISTINCT tx_hash) AS tx_count,

LEAD(block_timestamp) OVER (ORDER BY block_timestamp) AS next_block_timestamp

FROM

monad.testnet.fact_transactions

WHERE

block_timestamp::date >= '{{start_day}}'

AND date_trunc({{period_type}}, block_timestamp) <= '{{target_day}}'

GROUP BY

1, 2

period_start AS "Date",

AVG(tx_count / NULLIF(DATEDIFF('second', block_timestamp, next_block_timestamp), 0)) AS "AVG TPS",

LAG("AVG TPS") OVER (ORDER BY period_start) AS lag_avg_tps,

ROUND(100 * ("AVG TPS" - lag_avg_tps)/NULLIF(lag_avg_tps, 0), 2) AS "AVG TPS Change %",

MAX(tx_count / NULLIF(DATEDIFF('second', block_timestamp, next_block_timestamp), 0)) AS "MAX TPS",

LAG("MAX TPS") OVER (ORDER BY period_start) AS lag_max_tps,

ROUND(100 * ("MAX TPS" - lag_max_tps)/NULLIF(lag_max_tps, 0), 2) AS "MAX TPS Change %"

FROM

block_data

WHERE

next_block_timestamp IS NOT NULL

GROUP BY

1

ORDER BY

1 DESC;