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

,LABEL_TYPE

,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 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

)

,lst_aptos_price as (

-- with lst_all_price as (

-- select

-- TO_TIMESTAMP(value[0]::string) as avg_date

-- ,value[1] as avg_price

-- from (

-- select livequery.live.udf_api(

-- 'https://api.coingecko.com/api/v3/coins/avalanche-2/market_chart?vs_currency=usd&days=700&interval=daily&precision=3') as resp

-- )

-- ,lateral flatten (input => resp:data:prices)

-- )

-- select

-- date_trunc(week,avg_date) as avg_date