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 :: DATE AS DATE,

Platform,

Swapper,

TX_HASH,

COALESCE(AMOUNT_IN_USD, AMOUNT_OUT_USD) AS AMOUNT_USD,

AVG_PRICE * (gas_used * gas_unit_price / 1e8) AS tx_fee_usd

FROM aptos.defi.ez_dex_swaps

JOIN aptos.core.fact_transactions USING (TX_HASH)

LEFT JOIN tb1 AS p ON p.DATE = BLOCK_TIMESTAMP::DATE

-- WHERE EVENT_ADDRESS = '0x48271d39d0b05bd6efca2278f22277d6fcc375504f9839fd73f74ace240861af'

Platform,

COUNT(DISTINCT Swapper) AS Swappers,

COUNT(DISTINCT TX_HASH) AS Swaps,

SUM(AMOUNT_USD) AS Swap_Volume,

AVG(tx_fee_usd) AS Avg_Tx_Fee_USD

FROM tb2

GROUP BY Platform

ORDER BY Avg_Tx_Fee_USD ASC

LIMIT 10