avg(open) as avg_open,

avg(close) as avg_close,

(avg_open + avg_close)/2 as near_price

from crosschain.core.fact_hourly_prices

where id = 'wrapped-near'

group by 1),

table_1 as (select date_trunc('week', block_timestamp) as week,

count(distinct(tx_hash)) as tx_count,

sum(tx_count) over (order by week) as cumu_tx_count,

tx_count/604800 as tps,

sum(transaction_fee/1e24 * near_price) as fee_usd,

sum(fee_usd) over (order by week) as cumu_fee_usd,

avg(transaction_fee/1e24 * near_price) as avg_fee_usd,

count(distinct(tx_signer)) as wallet_count,

tx_count/wallet_count as avg_tx

from near.core.fact_transactions a

join price b on a.block_timestamp::date = b.date

where tx_status = 'Success'

group by 1)

select *,

(select avg(tx_count) from table_1) as avg_weekly_tx,

(select avg(tps) from table_1) as avg_weekly_tps,

(select avg(fee_usd) from table_1) as avg_weekly_fee,

(select avg(avg_fee_usd) from table_1) as avg_weekly_fee_per_tx,

(select avg(wallet_count) from table_1) as avg_weekly_wallets

from table_1

order by 1 desc