select

hour::date as price_date

,avg(price) as APT_price

from aptos.price.ez_hourly_token_prices

where symbol='APT'

group by 1

order by 1

,lst_new_users as (

select

sender as new_user

,min(block_timestamp)::date as min_date

from aptos.core.fact_transactions

where SUCCESS

group by 1

,lst_group_new_users as (

select

min_date

,count(DISTINCT new_user) as "New Users"

from lst_new_users

group by 1

order by 1

select

date_trunc(week,block_timestamp)::date as date

,"New Users"

,count( DISTINCT tx_hash ) as "TXs"

,"TXs"/24/60/60 as "Avg. Transactions/sec"

,count(DISTINCT sender) as Wallets

,sum(gas_unit_price*gas_used/1e8) as "Total Gas Received"

,avg("TXs") over (order by date rows between 6 preceding and current row) as seven_day_avg_transaction

,"Total Gas Received" / "TXs" as "Gas per Transaction Ratio"

,sum("New Users") over(order by date) as cum_new_users

from aptos.core.fact_transactions