SELECT

date_trunc('hour', hour) AS hour,

MAX(CASE WHEN asset_id = 'ethereum' THEN close END) AS eth_price_usd,

MAX(CASE WHEN asset_id = 'bitcoin' THEN close END) AS btc_price_usd

FROM crosschain.price.fact_prices_ohlc_hourly

WHERE provider = 'coingecko'

GROUP BY 1

),

transaction_data AS (

SELECT

date_trunc('hour', block_timestamp) AS BLOCK_TIMESTAMP,

tx_hash,

amount_in, -- WETH

amount_in_usd,

amount_out -- WBTC

FROM arbitrum.defi.ez_dex_swaps

WHERE Platform = 'uniswap-v2'

AND Pool_name = 'WBTC-WETH'

AND AMOUNT_OUT > 0

-- AND tx_hash = '0x7852d3a5bc43cb433ccd00583181f2c837adf22f3cd7ad0453536a16808df454'

),

price_at_transaction AS (

SELECT

hour,

eth_price_usd,

btc_price_usd

FROM hourly_prices

WHERE hour BETWEEN (SELECT MIN(BLOCK_TIMESTAMP) FROM transaction_data)

AND (SELECT MAX(BLOCK_TIMESTAMP) FROM transaction_data)

AND eth_price_usd > 0

AND btc_price_usd > 0

),