SELECT

avg(price) AS bnb_price_usd

FROM

bsc.price.ez_prices_hourly

WHERE

symbol = 'BNB'

AND hour >= '2024-01-01'

AND hour <= '2024-12-31'

),

swaps_with_gas AS (

SELECT

s.tx_hash,

t.tx_fee,

s.block_timestamp

FROM

bsc.defi.ez_dex_swaps s

JOIN bsc.core.fact_transactions t ON s.tx_hash = t.tx_hash

WHERE

t.block_timestamp >= '2024-01-01'

AND t.block_timestamp <= '2024-12-31'

AND t.status = 'SUCCESS'

),

swaps_with_price AS (

SELECT

s.tx_hash,

s.tx_fee,

s.block_timestamp,

u.bnb_price_usd

FROM

swaps_with_gas s

CROSS JOIN usd_conversion u

COUNT(DISTINCT s.tx_hash) AS total_swaps,

SUM(s.tx_fee) AS total_swap_fee_bnb,