SELECT

DATE_TRUNC('MINUTE',BLOCK_TIMESTAMP) DATE,

COUNT(*) "# TX PER MIN"

FROM optimism.core.fact_transactions

GROUP BY DATE

),

SUCCESSFUL_TXS AS (

SELECT

DATE_TRUNC('MINUTE',BLOCK_TIMESTAMP) DATE,

COUNT(*) "# SUCCESSFUL TX PER MIN"

FROM optimism.core.fact_transactions

WHERE STATUS = 'SUCCESS'

GROUP BY DATE

),

FAILED_TXS AS (

SELECT

DATE_TRUNC('MINUTE',BLOCK_TIMESTAMP) DATE,

COUNT(*) "# FAILED TX PER MIN"

FROM optimism.core.fact_transactions

WHERE STATUS = 'FAIL'

GROUP BY DATE

),

raw_data as (

SELECT

A.DATE, A."# TX PER MIN",

T."# SUCCESSFUL TX PER MIN",

F."# FAILED TX PER MIN"

FROM all_txs A

LEFT JOIN SUCCESSFUL_TXS T ON A.DATE=T.DATE

LEFT JOIN FAILED_TXS F ON A.DATE=F.DATE

WHERE A.DATE >= CURRENT_DATE - 30

select

sum("# TX PER MIN") "Total Transactions",