AS (SELECT Date_trunc('hour', block_timestamp) AS date,

Count(tx_id) / ( 60 ) AS succeed_txs

FROM flow.core.fact_events

WHERE tx_succeeded = 'true'

AND date >= '2022-05-09'

AND DATE < CURRENT_DATE

GROUP BY 1 ),

unsucceeded_txs

AS (SELECT Date_trunc('hour', block_timestamp) AS date,

Count(tx_id) / ( 60 ) AS unsucceed_txs

FROM flow.core.fact_events

WHERE tx_succeeded = 'false'

AND date >= '2022-05-09'

AND DATE < CURRENT_DATE

GROUP BY 1

)

SELECT succeeded_txs.date AS Date,

Round (succeed_txs,1) AS successful_tpm,

Round(( unsucceed_txs / ( successful_tpm + unsucceed_txs ) ) * 100, 2) AS unsuccessful_transaction_percent

FROM succeeded_txs

JOIN unsucceeded_txs

ON succeeded_txs.date = unsucceeded_txs.date