select

block_timestamp

,from_address as user_address

from blast.core.fact_transactions

where status = 'SUCCESS'

and block_timestamp > '2024-02-29'

group by 1,2

),

first_txs as (

select

user_address

, min(block_timestamp) as first_timestamp

from blast.core.fact_transactions

inner join (select distinct user_address from all_txs) users

on fact_transactions.from_address = users.user_address

where status = 'SUCCESS'

group by 1

),

aggregated as (

select

date_trunc('day', block_timestamp) as date

, iff(date_trunc(day, first_timestamp) = date_trunc(day, block_timestamp), '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