WITH hop_pools AS (

SELECT $1 AS pool_address, $2 AS token_symbol, $3 AS decimals

FROM (VALUES

('0x0ce6c85cf43553de10fc56ceca0aef6ff0dd444d', 'ETH', 18),

('0x022c5ce6f1add7423268d41e08df521d5527c2a0', 'USDC', 6)

)

),

hop AS (

SELECT tx_hash, block_timestamp,

SUBSTR(topics[1], 27) AS receiver,

ethereum.public.udf_hex_to_int(SUBSTR(data, 67, 64)) / POW(10, h.decimals) AS amount,

amount * IFF(token_symbol = 'ETH', price, 1) AS amount_usd, token_symbol AS symbol

FROM hop_pools h

JOIN base.core.fact_event_logs ON topics[0] = '0xc6c1e0630dbe9130cc068028486c0d118ddcea348550819defd5cb8c257f8a38' AND contract_address = pool_address

LEFT JOIN crosschain.price.ez_hourly_token_prices ON hour = TRUNC(block_timestamp, 'hour')

AND blockchain = 'ethereum' AND token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

),

eth_transfers AS (

SELECT tx_hash, block_timestamp, from_address, to_address,

eth_value AS amount

FROM base.core.fact_traces

WHERE amount > 0

--OR from_address = '0x13e46b2a3f8512ed4682a8fb8b560589fe3c2172'

),

lz_chains AS (

SELECT $1 AS chain, $2 AS chain_id

FROM (VALUES

('Ethereum', 101),

('BNB Chain', 102),

('Avalanche', 106),

('Aptos', 108),

('Polygon', 109),

('Arbitrum', 110),

('Optimism', 111),