bnb_price as (

select

trunc(hour,'day') as date,

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

),

eth_price as (

select

trunc(hour,'day') as date,

avg(price) as eth_price

from ethereum.core.fact_hourly_token_prices where symbol ='WETH'

and hour>=CURRENT_DATE-7

group by 1

),

bsc as (

SELECT

trunc(block_timestamp,'day') as days,

count(distinct tx_hash) as n_bsc_txs_days,

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

count(distinct from_address) as bsc_users,

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

avg(tx_fee*bnb_price) as avg_bsc_fees

from bsc.core.fact_transactions

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

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_days,

sum(n_arbitrum_txs_days) over (order by days) as cum_arbitrum_txs,

count(distinct from_address) as arbitrum_users,