SELECT

tx_hash

FROM base.core.fact_transactions

where to_address LIKE lower('0xcF77a3Ba9A5CA399B7c97c74d54e5b1Beb874E43')

), tab2 as (

SELECT

tx_hash,

'in' as flow,

to_address as pool,

block_timestamp,

from_address,

(raw_amount / power(10, decimals)) * price as swap_volume

FROM base.core.fact_token_transfers

LEFT outer JOIN

SELECT

date(hour) as day,

token_address,

symbol,

decimals,

median(price) as price

FROM base.price.ez_hourly_token_prices

GROUP BY 1,2,3,4

) on day = date(block_timestamp)

and token_address = contract_address

WHERE tx_hash in (SELECT * from tab1)

AND to_address in (

lower('0xbb7e170df05231f2ceab51c80f35fe68e47d3af4'), --

lower('0xbBf71AAD6E1F8D180D3B1D898528654c1d18b2fF'), -- fBOMB/AERO

lower('0x7f670f78B17dEC44d5Ef68a48740b6f8849cc2e6'), -- WETH/AERO

lower('0x2223F9FE624F69Da4D8256A7bCc9104FBA7F8f75'), -- AERO/USDbC

lower('0x6EAB8c1B93f5799daDf2C687a30230a540DbD636'), -- DAI/USDbC

lower('0x9e4CB8b916289864321661CE02cf66aa5BA63C94'), -- WETH/DEUS