block_timestamp,

from_address as user_address

from avalanche.core.fact_transactions

to_address = '0x5898c3e218a8501533d771c86e2fa37743ea2add'

and STATUS = 'SUCCESS'

),

first_txs as (

select

user_address

, min(block_timestamp) as first_timestamp

from avalanche.core.fact_transactions

inner join (select distinct user_address from all_txs) users

on fact_transactions.from_address = users.user_address

where STATUS = 'SUCCESS'

and fact_transactions.to_address = '0x5898c3e218a8501533d771c86e2fa37743ea2add'

group by 1

),

aggregated as (

select

date_trunc('week', block_timestamp) as date

, iff(first_timestamp ::date = block_timestamp ::date, 'New', 'Old') as category

, count(distinct user_address) as users

from all_txs

left join first_txs

using(user_address)

group by 1,2

select *

from aggregated

order by date desc, category desc