SELECT

DATE_TRUNC('day', block_timestamp) AS date,

COUNT(DISTINCT Tx_hash) AS txn_count,

SUM(TRANSACTION_FEE / POW(10, 24)) AS total_tx_fee

FROM near.core.fact_transactions

WHERE block_timestamp::date > CURRENT_DATE - INTERVAL '30 DAY'

GROUP BY date

),

price_data AS (

SELECT

DATE_TRUNC('day', Hour) AS date,

AVG(price) AS avg_price

FROM near.price.ez_prices_hourly

WHERE symbol = 'LINEAR'

GROUP BY date

),

daily_fees AS (

SELECT

tf.date,

tf.txn_count,

tf.total_tx_fee * pd.avg_price AS fee_usd

FROM transaction_fees tf

JOIN price_data pd ON tf.date = pd.date

SELECT

Date,

SUM(txn_count) AS total_txn_count,

SUM(fee_usd) AS total_fee_usd,

AVG(fee_usd) AS AVG_fee_usd

FROM daily_fees

GROUP BY 1