SELECT

token_address,

AVG(price) AS token_price,

DATE_TRUNC('day', hour) AS price_date

FROM aptos.price.ez_prices_hourly

GROUP BY token_address, DATE_TRUNC('day', hour)

SUM(n.AMOUNT * p.token_price) AS total_volume_usd,

SUM(n.AMOUNT) AS total_volume_apt,

COUNT(DISTINCT n.TX_HASH) AS total_transactions,

COUNT(DISTINCT n.FROM_ADDRESS) AS active_users

FROM aptos.core.ez_native_transfers n

LEFT JOIN price_data p

ON n.TOKEN_ADDRESS = p.token_address

AND DATE_TRUNC('day', n.BLOCK_TIMESTAMP) = p.price_date

WHERE n.BLOCK_TIMESTAMP >= DATE_TRUNC('day', CURRENT_DATE - 7);