from avalanche.core.fact_transactions

where STATUS='SUCCESS'

group by 1),

failavax as (select date( block_timestamp) as day, count(distinct tx_hash) as n_txn_avax

from avalanche.core.fact_transactions

where STATUS='FAIL'

group by 1),

new_avax_user as (select date(tm) as day, count(distinct from_address) as n_new_address,

sum(n_new_address) OVER(ORDER BY day asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_new_user

from (select from_address, min(block_timestamp) as tm

from avalanche.core.fact_transactions

group by 1)

group by 1)

select succavax.day , succavax.n_txn_avax as success_txn_vol, failavax.n_txn_avax as fail_txn_vol,(success_txn_vol/(success_txn_vol+fail_txn_vol))*100 as tx_success_rate_percent, cum_new_user as cum_new_user_avax

from succavax inner join failavax on failavax.day=succavax.day

inner join new_avax_user on new_avax_user.day=succavax.day