from aptos.core.fact_transactions

where SUCCESS = 'TRUE'

and block_timestamp::date >= '2023-01-01'

and TX_TYPE ilike '%user%'

,

base_table as ( select

sender as origin_from_address

, date_trunc('week', block_timestamp) as date

, min(date_trunc('week', block_timestamp)) over(partition by origin_from_address) as earliest_date

, datediff(

'week'

, min(date_trunc('week', block_timestamp)) over(partition by origin_from_address) -- earliest_date

, date_trunc('week', block_timestamp) -- current date in month

) as difference

from new

, count_new_users as(

select

earliest_date

, count(distinct origin_from_address) as new_users

from base_table

group by 1

, count_returning_users as(

select

earliest_date

, difference

, count(distinct origin_from_address) as existing_users

from base_table

where difference != 0