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,

amount_in_usd,

amount_in_usd / amount_in AS amm_price,

'Ethereum' AS chain

FROM ethereum.defi.ez_dex_swaps

WHERE Platform = 'uniswap-v2'

AND Pool_name = 'WBTC-WETH'

AND token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

AND token_out = '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599'

UNION ALL

SELECT

date_trunc('hour', block_timestamp) AS BLOCK_TIMESTAMP,

tx_hash,

amount_in,

amount_in_usd,

amount_in_usd / amount_in AS amm_price,

'Arbitrum' AS chain

FROM arbitrum.defi.ez_dex_swaps

WHERE Platform = 'uniswap-v2'

AND Pool_name = 'WBTC-WETH'