account_txs as (

select

block_timestamp

, sender as address

, 'Active Accounts' as category

from aptos.core.fact_transactions

-- where block_timestamp ::date > current_date() - interval '180 days'

union all

select

block_timestamp

, account_address as address

, 'New Accounts' as category

from aptos.core.fact_events

where success

and event_address = '0x1'

and event_module = 'account'

and event_resource = 'CoinRegisterEvent'

and event_data :type_info :module_name = '0x6170746f735f636f696e' -- aptos_coin

and sequence_number = 0

-- and block_timestamp ::date > current_date() - interval '180 days'

SELECT

date,

category,

avg(accounts) over (partition by category

order by date rows between 7 preceding and current row) as users