select distinct from_address as user, MIN(block_timestamp) as first_seen

from ronin.core.fact_transactions

group by from_address

),

daily_users as (

select

date_trunc('day', block_timestamp) as date,

count(distinct from_address) as active_users,

count(distinct case when t.block_timestamp = f.first_seen then from_address end) as new_users

from ronin.core.fact_transactions t

left join first_tx f ON t.from_address = f.user

where block_timestamp >= '2025-02-01' and block_timestamp < '2025-02-28'

group by date

select *

from daily_users

order by date;