SELECT

DATE_TRUNC('day', block_timestamp) AS transaction_day, -

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

COUNT(DISTINCT tx_hash) AS daily_transactions

FROM near.core.fact_transactions

WHERE tx_succeeded = 'true'

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

AND block_timestamp < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'

GROUP BY transaction_day

),

NEARPrice AS (

SELECT

DATE_TRUNC('day', hour) AS price_day,

AVG(price) AS near_price

FROM near.price.ez_prices_hourly

WHERE symbol = 'NEAR'

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

AND hour < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'

GROUP BY price_day

SELECT

df.transaction_day,

df.daily_transactions,

df.daily_fees_in_near,

df.daily_fees_in_near * np.near_price AS daily_fees_in_usd,

df.daily_fees_in_near / df.daily_transactions AS avg_fee_per_transaction_in_near,

ROUND((df.daily_fees_in_near / df.daily_transactions) * np.near_price, 4) AS avg_fee_per_transaction_in_usd

FROM

DailyFees df

JOIN

NEARPrice np

ON

df.transaction_day = np.price_day

ORDER BY

df.transaction_day;