with suctpm as (
select
date_trunc('minute', block_timestamp) as mnt,
'Succesful' as type,
count(distinct tx_id) as tx_cnt
from
flow.core.fact_transactions
where
TX_SUCCEEDED and block_timestamp > CURRENT_DATE-30
group by 1
), failtpm as (
select
date_trunc('minute', block_timestamp) as mnt,
'Failed' as type,
count(distinct tx_id) as tx_cnt
from
flow.core.fact_transactions
where
(not TX_SUCCEEDED) and block_timestamp > CURRENT_DATE-30
group by 1
), frates as (
select
s.mnt,
f.tx_cnt/(s.tx_cnt+f.tx_cnt) as "Failure Rate"
from
suctpm s join failtpm f on s.mnt=f.mnt
)
select avg("Failure Rate") as "Average Failure Rate" from frates