select

block_timestamp::date as date

,tx_hash

,sender as wallet

,event_address

,INITCAP(LABEL_TYPE) as Type

,iff( INITCAP(LABEL)='Move Dollar','Thala',INITCAP(LABEL)) as LABEL

,ADDRESS_NAME

,LABEL_SUBTYPE

from aptos.core.fact_events

join aptos.core.dim_labels on address = event_address

join aptos.core.fact_transactions using(tx_hash)

where SUCCESS=true

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/aptos/market_chart?vs_currency=usd&days=200&interval=daily&precision=3') as resp

)

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

)

select

date_trunc(day,avg_date) as avg_date

,avg(avg_price) as avg_price

from lst_all_price

group by 1

,lst_new as (

select

sender