WITH TotalFees AS (

SELECT

SUM(tx_fee) AS total_fees_in_matic,

COUNT(DISTINCT TX_HASH) AS total_transactions

FROM polygon.core.fact_transactions

WHERE status = 'SUCCESS'

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

),

MATICPrice AS (

SELECT

MEDIAN(price) AS matic_price

FROM polygon.price.ez_prices_hourly

WHERE symbol = 'MATIC'

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

SELECT

tf.total_transactions,

tf.total_fees_in_matic,

tf.total_fees_in_matic * mp.matic_price AS total_fees_in_usd,

tf.total_fees_in_matic / tf.total_transactions AS avg_fee_per_transaction_in_matic,

ROUND((tf.total_fees_in_matic / tf.total_transactions) * mp.matic_price,4)AS avg_fee_per_transaction_in_usd

FROM

TotalFees tf,

MATICPrice mp;