select

tx_signer

,min(block_timestamp)::date as min_date

from near.core.fact_transactions

where tx_succeeded = true

group by 1

,lst_new_users as (

select

min_date::date as date

,count(DISTINCT tx_signer) as "New Wallets"

from lst_new_wallet_raw

where min_date::date >= current_date-interval '1 week'

group by 1

,lst_price_raw as (

select

date_trunc(hour,HOUR) as price_date

-- ,token_contract

,avg(CLOSE) as avg_price

from near.price.fact_prices_ohlc_hourly

-- where token_contract='wrap.near'

where ASSET_ID='wrapped-near'

and price_date is not null

and hour::date >= current_date-interval '1 week'

group by 1

date_trunc(hour,block_timestamp) as date

,avg_price as "NEAR Price"

,count(DISTINCT tx_hash) as TXn

,count(DISTINCT tx_signer) as Wallets

,sum(TRANSACTION_FEE /1e24*"NEAR Price")/TXn as "TX Fee.Avg($)"