total_NEAR as (

SELECT count(TXN_HASH) as TOTAL , 'Near' as blockchain from flipside_prod_db.mdao_near.transactions),

failed_NEAR as (

SELECT count(TXN_HASH) as FAILED from flipside_prod_db.mdao_near.transactions WHERE substr(tx_receipt[0]:"outcome":"status",3,7)='Failure'),

total_ETH as (

SELECT count(TX_HASH) as TOTAL , 'Ethereum' as blockchain from ethereum.core.fact_transactions),

failed_ETH as (

SELECT count(TX_HASH) as FAILED from ethereum.core.fact_transactions WHERE STATUS!='SUCCESS'),

total_BSC as (

SELECT count(TX_HASH) as TOTAL , 'Binance Smart Chain' as blockchain from bsc.core.fact_transactions),

failed_BSC as (

SELECT count(TX_HASH) as FAILED from bsc.core.fact_transactions WHERE STATUS!='SUCCESS'),

total_SOLANA as (

SELECT count(TX_ID) as TOTAL , 'solana' as blockchain from solana.core.fact_transactions),

failed_SOLANA as (

SELECT count(TX_ID) as FAILED from solana.core.fact_transactions WHERE SUCCEEDED!='TRUE'),

total_Avalanche as (

SELECT count(TX_HASH) as TOTAL, 'Avalanche' as blockchain from avalanche.core.fact_transactions),

failed_Avalanche as (

SELECT count(TX_HASH) as FAILED from avalanche.core.fact_transactions WHERE STATUS!='SUCCESS')

SELECT TOTAL, FAILED, CAST(FAILED as FLOAT) * 100.0 / CAST(TOTAL as FLOAT) as Percent_of_FAILED ,blockchain

From total_NEAR,failed_NEAR

SELECT TOTAL, FAILED, CAST(FAILED as FLOAT) * 100.0 / CAST(TOTAL as FLOAT) as Percent_of_FAILED ,blockchain

From total_ETH,failed_ETH

SELECT TOTAL, FAILED, CAST(FAILED as FLOAT) * 100.0 / CAST(TOTAL as FLOAT) as Percent_of_FAILED ,blockchain

From total_BSC,failed_BSC

SELECT TOTAL, FAILED, CAST(FAILED as FLOAT) * 100.0 / CAST(TOTAL as FLOAT) as Percent_of_FAILED ,blockchain

From total_SOLANA,failed_SOLANA

SELECT TOTAL, FAILED, CAST(FAILED as FLOAT) * 100.0 / CAST(TOTAL as FLOAT) as Percent_of_FAILED ,blockchain

From total_Avalanche,failed_Avalanche