SELECT

DATE_TRUNC('day', block_timestamp) AS transaction_day,

SUM(tx_fee) AS daily_fees_in_bnb,

COUNT(DISTINCT tx_hash) AS daily_transactions

FROM bsc.core.fact_transactions

WHERE status = 'SUCCESS'

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

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

GROUP BY transaction_day

),

BNBPrice AS (

SELECT

DATE_TRUNC('day', hour) AS price_day,

AVG(price) AS bnb_price

FROM bsc.price.ez_prices_hourly

WHERE symbol = 'BNB'

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

df.daily_fees_in_bnb * bp.bnb_price AS daily_fees_in_usd,

df.daily_fees_in_bnb / df.daily_transactions AS avg_fee_per_transaction_in_bnb,

ROUND((df.daily_fees_in_bnb / df.daily_transactions) * bp.bnb_price, 4) AS avg_fee_per_transaction_in_usd

FROM

DailyFees df

JOIN

BNBPrice bp

ON

df.transaction_day = bp.price_day

ORDER BY

df.transaction_day;