COUNT(DISTINCT CASE WHEN substr(TX_RECEIPT[0]:"outcome":"status",3,7) ILIKE 'failure' THEN TXN_HASH END) AS "COUNT OF FAILED TXs", 'NEAR' AS Blockchain

FROM flipside_prod_db.mdao_near.transactions

UNION ALL

SELECT COUNT(DISTINCT CASE WHEN status = 'SUCCESS' THEN TX_HASH END) AS "COUNT OF SUCCEEDED TXs",

COUNT(DISTINCT CASE WHEN status = 'FAIL' THEN TX_HASH END) AS "COUNT OF FAILED TXs", 'ETHEREUM' AS Blockchain

FROM ethereum.core.fact_transactions

UNION ALL

SELECT COUNT(DISTINCT CASE WHEN status = 'SUCCESS' THEN TX_HASH END) AS "COUNT OF SUCCEEDED TXs",

COUNT(DISTINCT CASE WHEN status = 'FAIL' THEN TX_HASH END) AS "COUNT OF FAILED TXs", 'BSC' AS Blockchain

FROM bsc.core.fact_transactions

UNION ALL

SELECT COUNT(DISTINCT CASE WHEN TX_STATUS = 'SUCCEEDED' THEN TX_ID END) AS "COUNT OF SUCCEEDED TXs",

COUNT(DISTINCT CASE WHEN TX_STATUS = 'FAILED' THEN TX_ID END) AS "COUNT OF FAILED TXs", 'OSMOSIS' AS Blockchain

FROM osmosis.core.fact_transactions

UNION ALL

SELECT COUNT(DISTINCT CASE WHEN STATUS = 'SUCCESS' THEN TX_HASH END) AS "COUNT OF SUCCEEDED TXs",

COUNT(DISTINCT CASE WHEN STATUS = 'FAIL' THEN TX_HASH END) AS "COUNT OF FAILED TXs", 'AVALANCHE' AS Blockchain

FROM avalanche.core.fact_transactions

UNION ALL

SELECT COUNT(DISTINCT CASE WHEN TX_SUCCEEDED = 'TRUE' THEN TX_ID END) AS "COUNT OF SUCCEEDED TXs",

COUNT(DISTINCT CASE WHEN TX_SUCCEEDED = 'FALSE' THEN TX_ID END) AS "COUNT OF FAILED TXs", 'FLOW' AS Blockchain

FROM flow.core.fact_transactions