SELECT

DATE_TRUNC('hour', block_timestamp) AS transaction_hour,

COUNT(DISTINCT tx_hash) AS total_transactions,

SUM((gas_used * gas_unit_price) / 1e8) AS total_fees_in_apt

FROM aptos.core.fact_transactions

WHERE success = 'true'

AND DATE_TRUNC('month', block_timestamp) = '2024-10-01' -- Specify October 2024

GROUP BY transaction_hour

),

APTPrice AS (

SELECT

DATE_TRUNC('hour', hour) AS price_hour,

MEDIAN(price) AS apt_price

FROM aptos.price.ez_prices_hourly

WHERE symbol = 'APT'

AND DATE_TRUNC('month', hour) = '2024-10-01' -- Specify October 2024

GROUP BY price_hour

SELECT

hf.transaction_hour,

hf.total_transactions,

hf.total_fees_in_apt,

hf.total_fees_in_apt * ap.apt_price AS total_fees_in_usd,

hf.total_fees_in_apt / hf.total_transactions AS avg_fee_per_transaction_in_apt,

ROUND((hf.total_fees_in_apt / hf.total_transactions) * ap.apt_price, 4) AS avg_fee_per_transaction_in_usd

FROM

HourlyFees hf

JOIN

APTPrice ap

ON

hf.transaction_hour = ap.price_hour

ORDER BY

hf.transaction_hour;