first_action as (

select t.signers[0] as wal, min(block_timestamp) as min_date,

ADDRESS_NAME

from solana.fact_transactions t join solana.dim_labels l on t.instructions[0]:programId = l.address

where date(t.block_timestamp) >= '2021-06-01'

and l.label_subtype != 'token_contract'

and l.label != 'solana'

and t.succeeded = TRUE

group by wal, address_name

SELECT

date(min_date) as date,

address_name,

count(DISTINCT wal) as users

from (

SELECT

wal,

address_name,

min_date,

row_number() over (partition by wal order by min_date ASC) as rank

from first_action

)

where rank = 1

GROUP by date, address_name