select

block_timestamp::date as date

,tx_hash

,from_address as wallet

,CONTRACT_ADDRESS

,iff( INITCAP(LABEL_TYPE)='Token','Defi',INITCAP(LABEL_TYPE)) as Type

,PROJECT_NAME

,ADDRESS_NAME

,LABEL_SUBTYPE

from avalanche.core.fact_event_logs

join avalanche.core.dim_labels on address = CONTRACT_ADDRESS

join avalanche.core.fact_transactions using(tx_hash)

where TX_STATUS='SUCCESS'

and LABEL_TYPE!='token'

and LABEL_SUBTYPE !='general_contract'

-- and tx_type='user_transaction'

,lst_new as (

select

from_address

,min(block_timestamp)::date as min_date

from avalanche.core.fact_transactions

group by 1

)

-- ,Type

INITCAP(PROJECT_NAME) as lebel

,count(DISTINCT wallet) as Wallets

,count(DISTINCT tx_hash) as TXs

from lst_all

join lst_new on wallet=from_address

group by 1

order by WALLETS desc