SELECT

SUM(tx_fee_precise) AS total_fees_in_avax,

COUNT(DISTINCT tx_hash) AS total_transactions

FROM

avalanche.core.fact_transactions

WHERE

status = 'SUCCESS'

AND tx_fee > 0

AND DATE_TRUNC('month', block_timestamp) = DATE_TRUNC('month', CURRENT_DATE)

),

AVAXPrice AS (

SELECT

MEDIAN(price) AS avax_price

FROM

avalanche.price.ez_prices_hourly

WHERE

symbol = 'AVAX'

AND DATE_TRUNC('month', hour) = DATE_TRUNC('month', CURRENT_DATE)

SELECT

tf.total_transactions,

tf.total_fees_in_avax,

tf.total_fees_in_avax * ap.avax_price AS total_fees_in_usd,

tf.total_fees_in_avax / tf.total_transactions AS avg_fee_per_transaction_in_avax,

ROUND((tf.total_fees_in_avax / tf.total_transactions) * ap.avax_price, 4) AS avg_fee_per_transaction_in_usd

FROM

TotalFees tf,

AVAXPrice ap;