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_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=365&interval=daily&precision=3') as resp

)

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

)

select

date_trunc(week,avg_date) as avg_date

,avg(avg_price) as avg_price

from lst_all_price

group by 1