SELECT

AVG(price) AS apt_price_usd

FROM

aptos.price.ez_prices_hourly

WHERE

symbol = 'APT'

AND hour >= CURRENT_DATE - INTERVAL '1 year'

),

cex_wallets AS (

SELECT

DISTINCT ADDRESS

FROM

aptos.core.dim_labels

WHERE

LABEL_TYPE = 'cex'

),

user_transactions AS (

SELECT

BLOCK_TIMESTAMP AS block_timestamp,

FROM_ADDRESS AS wallet_address,

TX_HASH,

'Outflow' AS transaction_type,

-(AMOUNT / POWER(10, 8)) AS volume

FROM

aptos.core.ez_native_transfers

WHERE

SUCCESS = 'TRUE'

AND TO_ADDRESS IN (

SELECT

ADDRESS

FROM

cex_wallets

)

AND BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL '1 year'

UNION