SELECT

SUM(transaction_fee / POW(10, 24)) AS total_fees_in_near,

COUNT(DISTINCT tx_hash) AS total_transactions

FROM near.core.fact_transactions

WHERE tx_succeeded = 'true'

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

),

NEARPrice AS (

SELECT

AVG(price) AS near_price

FROM near.price.ez_prices_hourly

WHERE symbol = 'NEAR'

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

SELECT

tf.total_transactions,

tf.total_fees_in_near,

tf.total_fees_in_near * np.near_price AS total_fees_in_usd,

tf.total_fees_in_near / tf.total_transactions AS avg_fee_per_transaction_in_near,

ROUND((tf.total_fees_in_near / tf.total_transactions) * np.near_price, 4) AS avg_fee_per_transaction_in_usd

FROM

TotalFees tf,

NEARPrice np;