bsc as (

SELECT

trunc(block_timestamp,'day') as days,

count(distinct tx_hash) as n_bsc_txs,

count(case when status='SUCCESS' then 1 end) as success_bsc_txs,

count(case when status='FAIL' then 1 end) as fail_bsc_txs

from bsc.core.fact_transactions

where block_timestamp>=CURRENT_DATE-INTERVAL '7 DAYS'

group by 1

),

arbitrum as (

SELECT

trunc(block_timestamp,'day') as days,

count(distinct tx_hash) as n_arbitrum_txs,

count(case when status='SUCCESS' then 1 end) as success_arbitrum_txs,

count(case when status='FAIL' then 1 end) as fail_arbitrum_txs

from arbitrum.core.fact_transactions

where block_timestamp>=CURRENT_DATE-INTERVAL '7 DAYS'

and tx_fee<1

group by 1

select

bsc.days,

success_bsc_txs/n_bsc_txs as bsc_success_rate,

fail_bsc_txs/n_bsc_txs as bsc_fail_rate,

success_arbitrum_txs/n_arbitrum_txs as arbitrum_success_rate,

fail_arbitrum_txs/n_arbitrum_txs as arbitrum_fail_rate

from bsc, arbitrum a where bsc.days=a.days

order by 1 asc