SELECT

date_trunc('day', block_timestamp) as transaction_date,

from_address as user

from core.core.fact_transactions

),

first_transaction as(

user,

min(transaction_date) as first_seen_date

FROM daily_transactions

group by user

),

address_activity as (

dt.transaction_date,

dt.user,

case

when dt.transaction_date = ft.first_seen_date then 'new'

else 'returning'

end as activity_type

FROM daily_transactions dt

join first_transaction ft

on dt.user = ft.user

SELECT

transaction_date,

count(DISTINCT case when activity_type = 'new' then user end) as new_user,

count(DISTINCT case when activity_type = 'returning' then user end) as returning_user,

count(distinct user) as active_user,

sum(active_user) over (order by transaction_date) as cumulative_active_addresses,

sum(new_user) over (order by transaction_date) as cumulative_new_addresses

FROM address_activity

WHERE transaction_date >='2024-01-01'

GROUP BY transaction_date

order by 1 DESC