SELECT

e.tx_hash,

c.tx_fee,

c.block_timestamp

FROM ethereum.defi.ez_dex_swaps e

JOIN ethereum.core.fact_transactions c

ON e.tx_hash = c.tx_hash

WHERE e.pool_name IN (

'USD0-LBTC 3000 60 UNI-V3 LP',

'WBTC-LBTC 100 1 UNI-V3 LP',

'eBTC-LBTC 500 10 UNI-V3 LP',

'LBTC-WETH',

'LBTC-cbBTC 500 10 UNI-V3 LP',

'WBTC-LBTC 500 10 UNI-V3 LP'

)

),

eth_price_daily AS (

SELECT

DATE_TRUNC('day', hour) AS date,

AVG(price) AS avg_eth_price

FROM ethereum.price.ez_prices_hourly

WHERE symbol = 'WETH'

GROUP BY DATE_TRUNC('day', hour)

),

aggregated_eth_trades AS (

SELECT

SUM(t.tx_fee * p.avg_eth_price) AS total_fees_usd,

AVG(t.tx_fee * p.avg_eth_price) AS avg_fees_usd

FROM eth_trades t

LEFT JOIN eth_price_daily p

ON DATE_TRUNC('day', t.block_timestamp) = p.date

SELECT

total_fees_usd,

avg_fees_usd