SELECT DATE_TRUNC('hour',BLOCK_TIMESTAMP) DATE,

COUNT(*) total_tran

FROM flow.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= '2022-05-09'

GROUP BY DATE

),

TRUE_TRAN AS (

SELECT DATE_TRUNC('hour',BLOCK_TIMESTAMP) DATE,

COUNT(*) true_tran

FROM flow.core.fact_transactions

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

GROUP BY DATE

),

FALSE_TRAN AS (

SELECT DATE_TRUNC('hour',BLOCK_TIMESTAMP) DATE,

COUNT(*) false_tran

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,

ROW_NUMBER() OVER(ORDER BY a.date ASC) AS Row_,

A.total_tran "Total Transactions",

T.true_tran "Succeeded Transactions",

t.true_tran*100/a.total_tran "Succeeded Transactions Rate (%)",

sum(t.true_tran) over (order by a.date) as true_cumulative,

true_cumulative/row_ "Successful Transactions Mean Overtime",

F.false_tran

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

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

order by row_

-- )