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 >= '2024-01-01'

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 total_fee_usd,

(tf.total_tx_fee / tf.txn_count) * 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