NEAR_CTE as (SELECT case when tx_receipt[0]:outcome:status:Failure is not NULL THEN 'FAIL' ELSE 'SUCCESS' end as STATUS, TXN_HASH, BLOCK_TIMESTAMP

from flipside_prod_db.mdao_near.transactions where block_timestamp::date>='2022-06-18' and block_timestamp::date<='2022-07-17')

select 'NEAR' as blockchain, block_timestamp::date as date, count_if(status = 'FAIL') as Fail, (Fail/count(TXN_HASH))*100 as fail_percentage

from NEAR_CTE where block_timestamp::date>='2022-06-18' and block_timestamp::date<='2022-07-17'

group by 1,2

union

select 'Ethereum' as blockchain, block_timestamp::date as date, count_if(status = 'FAIL') as Fail, (Fail/count(TX_HASH))*100 as fail_percentage

from ethereum.core.fact_transactions where block_timestamp::date>='2022-06-18' and block_timestamp::date<='2022-07-17'

group by 1,2

union

select 'Avalanche' as blockchain, block_timestamp::date as date, count_if(status = 'FAIL') as Fail, (Fail/count(TX_HASH))*100 as fail_percentage

from avalanche.core.fact_transactions where block_timestamp::date>='2022-06-18' and block_timestamp::date<='2022-07-17'

group by 1,2

union

select 'FLOW' as blockchain, block_timestamp::date as date, count_if(TX_SUCCEEDED = 'FALSE') as Fail, (Fail/count(TX_ID))*100 as fail_percentage

from flow.core.fact_transactions where block_timestamp::date>='2022-06-18' and block_timestamp::date<='2022-07-17'

group by 1,2

union

select 'Harmony' as blockchain, block_timestamp::date as date, count_if(status = 'FALSE') as Fail, (Fail/count(TX_HASH))*100 as fail_percentage

from flipside_prod_db.mdao_harmony.txs where block_timestamp::date>='2022-06-18' and block_timestamp::date<='2022-07-17'

group by 1,2

union

select 'Osmosis' as blockchain, block_timestamp::date as date, count_if(TX_STATUS = 'FAILED') as Fail, (Fail/count(TX_ID))*100 as fail_percentage