SELECT

b.tx_hash,

c.tx_fee,

c.block_timestamp

FROM base.defi.ez_dex_swaps b

JOIN base.core.fact_transactions c

ON b.tx_hash = c.tx_hash

WHERE b.pool_name IN (

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

'cbBTC-0xeca...11c1 0 1 ASLP',

'Balancer LBTC/cbBTC',

'cbBTC-LBTC'

)

),

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_base_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 base_trades t

LEFT JOIN eth_price_daily p

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

SELECT

total_fees_usd,

avg_fees_usd

FROM aggregated_base_trades;