SELECT DATE_TRUNC('MINUTE',BLOCK_TIMESTAMP) DATE,

COUNT(*) "# OF TRAN PER MIN"

FROM flow.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= '2022-05-09'

GROUP BY DATE

),

TRUE_TRAN AS (

SELECT DATE_TRUNC('MINUTE',BLOCK_TIMESTAMP) DATE,

COUNT(*) "# OF TRUE TRAN PER MIN"

FROM flow.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= '2022-05-09' AND TX_SUCCEEDED=TRUE

GROUP BY DATE

),

FALSE_TRAN AS (

SELECT DATE_TRUNC('MINUTE',BLOCK_TIMESTAMP) DATE,

COUNT(*) "# OF FALSE TRAN PER MIN"

FROM flow.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= '2022-05-09' AND TX_SUCCEEDED=FALSE

GROUP BY DATE--,TX_SUCCEEDED

),

raw_data as (

SELECT A.DATE, A."# OF TRAN PER MIN",

T."# OF TRUE TRAN PER MIN",

F."# OF FALSE TRAN PER MIN"

FROM ALL_TRAN A LEFT JOIN TRUE_TRAN T ON A.DATE=T.DATE

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

)

select sum("# OF TRAN PER MIN") "Total Transactions",

round (avg("# OF TRAN PER MIN"),0) "Total Avg Tran Per Min",

sum ("# OF TRUE TRAN PER MIN") "Total Succeeded Transactions",

round(avg("# OF TRUE TRAN PER MIN"),0) "Average Succeeded Transactions",

round(100* sum ("# OF TRUE TRAN PER MIN")/sum("# OF TRAN PER MIN"),1) as "% (Succeeded / Total) Transactions",

sum("# OF FALSE TRAN PER MIN") "Total Failed Transactions",

round(avg("# OF FALSE TRAN PER MIN"),0) "Average Failed Transactions",

round(100*sum("# OF FALSE TRAN PER MIN")/sum("# OF TRAN PER MIN"),1) as "% (Failed / Total) Transactions"