select

tx_signer

,min(block_timestamp)::date as min_date

from near.core.fact_transactions

where tx_succeeded = true

group by 1

,lst_new_users as (

select

min_date

,tx_signer

from lst_new_wallet_raw

where min_date::date >= current_date-interval '1 week'

,lst_all as (

select

tx_signer as new_wallet

,tx_receiver as Rc

,case

when TX_RECEIVER ilike '%sharddog%' then 'ShardDog'

when TX_RECEIVER = 'inscription.near' then 'NEAT'

when TX_RECEIVER ilike '%herewallet%' or TX_RECEIVER ilike '%tg' then 'Here Wallet'

when TX_RECEIVER='jars.sweat' then 'Sweat Еconomy'

when PROJECT_NAME is null then 'Other'

else INITCAP(PROJECT_NAME)

end as project

from near.core.fact_transactions

left join near.core.dim_address_labels on address = tx_receiver

where block_timestamp::date >= current_date-interval '1 week'

and tx_signer in (select tx_signer from lst_new_users)

and tx_succeeded = true

and (label_type != 'token' or label_type is null or LABEL_TYPE!='chadmin')

)

select top 20