select

account,

min(date(block_timestamp)) as first_txn_date

from stellar.core.fact_transactions

group by account

select

date(t.block_timestamp) as transaction_date,

count(distinct t.account) as daily_unique_users,

count(distinct case

when date(t.block_timestamp) = f.first_txn_date

then t.account

end) as new_users,

count(distinct case

when date(t.block_timestamp) > f.first_txn_date

then t.account

end) as recurring_users

from stellar.core.fact_transactions t

left join first_txn_dates f

on t.account = f.account

group by transaction_date

order by transaction_date desc;