SELECT

t.tx_fee AS fee_in_eth,

COUNT(s.tx_hash) AS number_of_swaps

FROM

optimism.core.fact_transactions t

JOIN

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

WHERE

t.status = 'SUCCESS'

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

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

GROUP BY t.tx_fee

),

ETHPrice AS (

SELECT

MEDIAN(price) AS eth_price

FROM

optimism.price.ez_prices_hourly

WHERE

symbol = 'ETH'

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

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

SELECT

SUM(sf.fee_in_eth) AS total_swap_fees_in_eth,

SUM(sf.fee_in_eth) * (SELECT ep.eth_price FROM ETHPrice ep) AS total_swap_fees_in_usd,

SUM(sf.number_of_swaps) AS total_number_of_swaps,

ROUND(SUM(sf.fee_in_eth) / SUM(sf.number_of_swaps), 4) AS avg_fee_per_swap_in_eth,

ROUND((SUM(sf.fee_in_eth) / SUM(sf.number_of_swaps)) * (SELECT ep.eth_price FROM ETHPrice ep), 4) AS avg_fee_per_swap_in_usd

FROM

SwapFees sf;