SELECT

date_trunc('hour', hour) AS hour,

AVG(CASE WHEN name = 'ethereum' THEN price END) AS eth_price_usd,

AVG(CASE WHEN name = 'bitcoin' THEN price END) AS btc_price_usd

FROM crosschain.price.ez_prices_hourly

GROUP BY 1

),

transaction_data AS (

SELECT

date_trunc('hour', block_timestamp) AS BLOCK_TIMESTAMP,

tx_hash,

amount_in, -- WETH

amount_in_usd,

amount_in_usd / amount_in AS amm_price -- AMM price for WETH/WBTC

FROM ethereum.defi.ez_dex_swaps

WHERE Platform = 'uniswap-v2'

AND Pool_name = 'WBTC-WETH'

AND token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

AND token_out = '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599'

),

transaction_fees AS (

SELECT

tf.tx_hash,

tf.tx_fee_precise,

hp.eth_price_usd

FROM ethereum.core.fact_transactions tf

JOIN hourly_prices hp ON date_trunc('hour', tf.block_timestamp) = hp.hour

WHERE tf.tx_hash IN (SELECT tx_hash FROM transaction_data)

),

price_at_transaction AS (

SELECT

hour,