SELECT

DATE_TRUNC('day', block_timestamp) AS transaction_day,

SUM(tx_fee) AS daily_fees_in_avax,

COUNT(DISTINCT tx_hash) AS daily_transactions

FROM avalanche.core.fact_transactions

WHERE

status = 'SUCCESS'

AND tx_fee > 0

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

AND block_timestamp < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'

GROUP BY transaction_day

),

AVAXPrice AS (

SELECT

DATE_TRUNC('day', hour) AS price_day,

MEDIAN(price) AS avax_price

FROM avalanche.price.ez_prices_hourly

WHERE

symbol = 'AVAX'

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

AND hour < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'

GROUP BY price_day

SELECT

df.transaction_day,

df.daily_transactions,

df.daily_fees_in_avax,

df.daily_fees_in_avax * ap.avax_price AS daily_fees_in_usd,

df.daily_fees_in_avax / df.daily_transactions AS avg_fee_per_transaction_in_avax,

ROUND((df.daily_fees_in_avax / df.daily_transactions) * ap.avax_price, 4) AS avg_fee_per_transaction_in_usd

FROM

DailyFees df

JOIN

AVAXPrice ap

ON