date_trunc (day,block_timestamp) as date,

count (case when tx_succeeded != 'TRUE' then 1 end) as Failed_TX,

count (case when tx_succeeded = 'TRUE' then 1 end) as Success_TX,

(Success_TX / (Success_TX + Failed_TX)) * 100 as Sucess_Rate

from flow.core.fact_transactions

where block_timestamp >= '2022-01-01'

and block_timestamp::date != CURRENT_DATE

group by 1,2

union ALL

select 'Avalanche' as chain,

date_trunc (day,block_timestamp) as date,

count (case when STATUS != 'SUCCESS' then 1 end) as Failed_TX,

count (case when STATUS = 'SUCCESS' then 1 end) as Success_TX,

(Success_TX / (Success_TX + Failed_TX)) * 100 as Success_Rate

from avalanche.core.fact_transactions

where block_timestamp >= '2022-01-01'

and block_timestamp::date != CURRENT_DATE

group by 1,2

union ALL

select 'Ethereum' as chain,

date_trunc (day,block_timestamp) as date,

count (case when STATUS != 'SUCCESS' then 1 end) as Failed_TX,

count (case when STATUS = 'SUCCESS' then 1 end) as Success_TX,

(Success_TX / (Success_TX + Failed_TX)) * 100 as Success_Rate

from ethereum.core.fact_transactions

where block_timestamp >= '2022-01-01'

and block_timestamp::date != CURRENT_DATE

group by 1,2

union ALL