SELECT

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

COUNT(DISTINCT TX_HASH) AS total_transactions

FROM aptos.core.fact_transactions

WHERE success = 'true'

AND block_timestamp >= CURRENT_DATE - INTERVAL '1 MONTH'

),

APTPrice AS (

SELECT

avg(price) AS apt_price

FROM aptos.price.ez_prices_hourly

WHERE symbol = 'APT'

AND hour >= CURRENT_DATE - INTERVAL '1 MONTH'

SELECT

tf.total_transactions,

tf.total_fees_in_apt,

tf.total_fees_in_apt * ap.apt_price AS total_fees_in_usd,

tf.total_fees_in_apt / tf.total_transactions AS avg_fee_per_transaction_in_apt,

(tf.total_fees_in_apt / tf.total_transactions) * ap.apt_price AS avg_fee_per_transaction_in_usd

FROM

TotalFees tf,

APTPrice ap;