with maintable as (
select 'Optimism' as blockchain,
count (case when STATUS != 'SUCCESS' then 1 end) as Failed_TX,
count (case when STATUS = 'SUCCESS' then 1 end) as Success_TX,
(Failed_TX / (Success_TX + Failed_TX)) * 100 as Failure_Rate
from optimism.core.fact_transactions
union ALL
select 'Ethereum' as blockchain,
count (case when STATUS != 'SUCCESS' then 1 end) as Failed_TX,
count (case when STATUS = 'SUCCESS' then 1 end) as Success_TX,
(Failed_TX / (Success_TX + Failed_TX)) * 100 as Failure_Rate
from ethereum.core.fact_transactions)
select blockchain, avg (failure_rate) as Average_Failure_Rate from maintable group by 1