WITH base as (

SELECT date_trunc('month',block_timestamp::date) AS date ,

COUNT(DISTINCT tx_hash) AS tx_count ,

(SELECT COUNT(DISTINCT tx_hash)/1440 FROM optimism.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01' ) AS jan_tpm ,

(SELECT COUNT(DISTINCT tx_hash)/1440 FROM optimism.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01' ) AS jul_tpm ,

(SELECT COUNT(DISTINCT tx_hash)/1440 FROM optimism.core.fact_transactions WHERE block_timestamp >= '2023-09-01' AND block_timestamp < '2023-10-01' ) AS sept_tpm ,

COUNT(DISTINCT BLOCK_NUMBER) AS block_count,

tx_count / block_count AS tx_per_block,

(block_count/24) block_per_hour,

(tx_count/86400) AS TPS,

(tx_count/1440) as TPM,

(tx_count/24) AS TPH ,

Lag(TPM) OVER (ORDER BY date) AS lag ,

TPM - lag AS change,

change*100 / TPM AS change_perc

from optimism.core.fact_transactions

where block_timestamp >= '2023-01-01'

AND block_timestamp < '2023-10-01'

and status = 'SUCCESS'

group by 1

order by 1

)

SELECT * FROM base