with pricet as (

SELECT

TO_TIMESTAMP(value[0]::string) as p_date,

'APT' as symbol,

value[1] as usd_price

from (

SELECT livequery.live.udf_api(

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

,LATERAL FLATTEN (input => resp:data:prices)

WHERE p_date >= Current_date() - 90

),

cext as (

SELECT

DISTINCT ADDRESS ,

FROM aptos.core.dim_labels

where LABEL_TYPE = 'cex'

and LABEL_SUBTYPE = 'hot_wallet'

SELECT

'OutFlow' as type,

ADDRESS_NAME,

count(DISTINCT TX_HASH) as txs,

count(DISTINCT TO_ADDRESS) as users,

sum(AMOUNT/1e8) as amount,

sum((AMOUNT/1e8) * usd_price) as usd_amount

FROM aptos.core.ez_native_transfers

JOIN pricet on BLOCK_TIMESTAMP::date = p_date

join cext on FROM_ADDRESS = ADDRESS

WHERE TOKEN_ADDRESS = '0x1::aptos_coin::AptosCoin'

and SUCCESS = 'TRUE'