SELECT

DATE_TRUNC('day', HOUR) AS Date,

AVG(PRICE) AS AVG_PRICE

FROM aptos.price.ez_prices_hourly

WHERE SYMBOL = 'APT'

GROUP BY 1

),

tb2 AS (

SELECT

BLOCK_TIMESTAMP,

AMOUNT_IN_USD,

AMOUNT_OUT_USD,

(gas_used * gas_unit_price / 1e8) as tx_fee_apt, -- Credit to Afonso https://flipsidecrypto.xyz/Afonso_Diaz/q/PexBTMyRZ6ef/base-total-swap

AVG_PRICE * tx_fee_apt as tx_fee_usd

FROM aptos.defi.ez_dex_swaps

JOIN aptos.core.fact_transactions USING (TX_HASH)

LEFT JOIN tb1 ON DATE = BLOCK_TIMESTAMP::DATE

WHERE EVENT_ADDRESS='0x48271d39d0b05bd6efca2278f22277d6fcc375504f9839fd73f74ace240861af'

-- AND block_timestamp::DATE >= '2024-01-01'

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS Date,

AVG(COALESCE(AMOUNT_IN_USD, AMOUNT_OUT_USD)) AS AVG_swap_Volume_USD,

AVG(tx_fee_usd) AS AVG_tx_fee_usd

FROM tb2

GROUP BY 1