SELECT

bs.tx_hash,

c.tx_fee,

c.block_timestamp

FROM bsc.defi.ez_dex_swaps bs

JOIN bsc.core.fact_transactions c

ON bs.tx_hash = c.tx_hash

WHERE bs.pool_name IN (

'BTCB-LBTC 500 10 PCS-V3 LP',

'0xad3...15ff-WBNB',

'LBTC-WBNB',

'0x1ff...ca1e-USDT',

'WBNB-LBTC',

'LBTC-DAI',

'WBNB-LBTC 10000 200 PCS-V3 LP',

'LBTC-USDT'

)

),

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_bsc_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 bsc_trades t

LEFT JOIN eth_price_daily p

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

SELECT