SELECT

AVG(price) AS apt_price_usd

FROM

aptos.price.ez_prices_hourly

WHERE

symbol = 'APT'

),

cex_wallets AS (

SELECT

DISTINCT owner_address

FROM

aptos.core.dim_aptos_names

WHERE

domain ILIKE 'petra'

),

user_transactions AS (

SELECT

BLOCK_TIMESTAMP AS block_timestamp,

ACCOUNT_ADDRESS AS wallet_address,

TX_HASH,

'Outflow' AS transaction_type,

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

FROM

aptos.core.fact_transactions

WHERE

SUCCESS = 'TRUE'

AND TO_ADDRESS IN (

SELECT

owner_address

FROM

cex_wallets

)

UNION

SELECT

BLOCK_TIMESTAMP AS block_timestamp,