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,

AVG(TRANSACTION_FEE / POW(10, 24)) AS avg_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'

AND Date > Current_date - INTERVAL '30 DAY'

GROUP BY date

),

daily_fees AS (

SELECT

tf.date,

tf.txn_count,

tf.total_tx_fee * pd.avg_price AS total_fee_usd,

tf.avg_tx_fee * pd.avg_price AS avg_fee_usd

FROM transaction_fees tf

JOIN price_data pd ON tf.date = pd.date

SELECT

date,

txn_count,

total_fee_usd,

avg_fee_usd

FROM daily_fees