With all_txs as (

select

block_timestamp

, signers[0] as user_address

from solana.core.fact_events

where succeeded

and fact_events.program_id = 'RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR'

and block_timestamp ::date > current_date() - interval '{{days}} days'

),

first_txs as (

select

user_address

, min(block_timestamp) as first_timestamp

from solana.core.fact_events

inner join (select distinct user_address from all_txs) users

on fact_events.signers[0] = users.user_address

where succeeded

and fact_events.program_id = 'RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR'

group by 1

),

aggregated as (

select

date_trunc('{{Granularly}}', block_timestamp) as date

, iff(first_timestamp ::date = block_timestamp ::date, 'New Users', 'Returning Users') 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