SELECT

t.transaction_fee / POW(10, 24) AS fee_in_near,

COUNT(s.tx_hash) AS number_of_swaps

FROM

near.core.fact_transactions t

JOIN

near.defi.ez_dex_swaps s ON t.tx_hash = s.tx_hash

WHERE

t.tx_succeeded = TRUE

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

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

GROUP BY t.transaction_fee

),

NEARPrice AS (

SELECT

MEDIAN(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'

SELECT

SUM(sf.fee_in_near) AS total_swap_fees_in_near,

SUM(sf.fee_in_near) * (SELECT np.near_price FROM NEARPrice np) AS total_swap_fees_in_usd,

SUM(sf.number_of_swaps) AS total_number_of_swaps,

ROUND(SUM(sf.fee_in_near) / SUM(sf.number_of_swaps), 4) AS avg_fee_per_swap_in_near,

ROUND((SUM(sf.fee_in_near) / SUM(sf.number_of_swaps)) * (SELECT np.near_price FROM NEARPrice np), 4) AS avg_fee_per_swap_in_usd

FROM

SwapFees sf;