date_trunc('hour', block_timestamp) as hour,

median(AMOUNT_USDBC / AMOUNT_AERO) as areo_price,

count(*) as swaps

FROM (

SELECT

block_timestamp,

tx_hash,

sum(CASE when contract_address LIKE lower('0xd9aAEc86B65D86f6A7B5B1b0c42FFA531710b6CA') then raw_amount / power(10, 6) else 0 end) as amount_usdbc,

sum(CASE when contract_address LIKE lower('0x940181a94A35A4569E4529A3CDfB74e38FD98631') then raw_amount / power(10, 18) else 0 end) as amount_aero

FROM base.core.fact_token_transfers

WHERE tx_hash in (

SELECT

tx_hash

FROM base.core.fact_transactions

where to_address LIKE lower('0xcF77a3Ba9A5CA399B7c97c74d54e5b1Beb874E43')

AND (

to_address LIKE '0x2223f9fe624f69da4d8256a7bcc9104fba7f8f75'

or from_address LIKE '0x2223f9fe624f69da4d8256a7bcc9104fba7f8f75'

GROUP BY 1,2

WHERE AMOUNT_USDBC > 0

AND AMOUNT_AERO > 0

GROUP BY 1