bnb_price as (

select

trunc(hour,'day') as day,

avg(price) as bnb_price

from ethereum.core.fact_hourly_token_prices where token_address=lower('0x418D75f65a02b3D53B2418FB8E1fe493759c7605')

--and hour>=CURRENT_DATE-7

group by 1

),

bsc as (

SELECT

trunc(block_timestamp,'day') as date,

count(distinct tx_hash) as n_bsc_txs_days,

sum(n_bsc_txs_days) over (order by date) as cum_bsc_txs,

count(distinct from_address) as bsc_users,

sum(bsc_users) over (order by date) as cum_bsc_users,

avg(tx_fee*bnb_price) as avg_bsc_fees,

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

join bnb_price y on trunc(block_timestamp,'day')=y.day

--where block_timestamp>=CURRENT_DATE-INTERVAL '7 DAYS'

group by 1

select

bsc.date,

n_bsc_txs_days,

success_bsc_txs/n_bsc_txs_days as bsc_success_rate,

fail_bsc_txs/n_bsc_txs_days as bsc_fail_rate,

cum_bsc_txs,

bsc_users,

cum_bsc_users,

from bsc

order by 1 asc