select

a.tx_hash,

a.block_timestamp,

-- case

-- when b.CONTRACT_ADDRESS = '0xe38faf9040c7f09958c638bbdb977083722c5156' then 'WETH'

-- when b.CONTRACT_ADDRESS = '0x56900d66d74cb14e3c86895789901c9135c95b16' then 'DAI'

-- when b.CONTRACT_ADDRESS = '0x25d8039bb044dc227f741a9e381ca4ceae2e6ae8' then 'USDC'

-- when b.CONTRACT_ADDRESS = lower('0x2057C8ECB70Afd7Bee667d76B4CD373A325b1a20') then 'USDT'

-- when b.CONTRACT_ADDRESS = lower('0xa492d3596e8391E376D4f5a5cBA5C077B890b094') then 'WBTC'

-- end as token

b.contract_address,

case

when b.CONTRACT_ADDRESS = '0x5c2048094baade483d0b1da85c3da6200a88a849' then 'Hop-LP-ETH'

when b.CONTRACT_ADDRESS = '0x22d63a26c730d49e5eab461e4f5de1d8bdf89c92' then 'Hop-LP-DAI'

when b.CONTRACT_ADDRESS = '0x2e17b8193566345a2dd467183526dedc42d2d5a8' then 'Hop-LP-USDC'

when b.CONTRACT_ADDRESS = '0xf753a50fc755c6622bbcaa0f59f0522f264f006e' then 'Hop-LP-USDT'

end as token,

case

when b.CONTRACT_ADDRESS = '0x5c2048094baade483d0b1da85c3da6200a88a849' then 'WETH'

when b.CONTRACT_ADDRESS = '0x22d63a26c730d49e5eab461e4f5de1d8bdf89c92' then 'DAI'

when b.CONTRACT_ADDRESS = '0x2e17b8193566345a2dd467183526dedc42d2d5a8' then 'USDC'

when b.CONTRACT_ADDRESS = '0xf753a50fc755c6622bbcaa0f59f0522f264f006e' then 'USDT'

end as symbol,

raw_amount/(pow(10,18)) as amount,

case

when topics[0] = '0x189c623b666b1b45b83d7178f39b8c087cb09774317ca2f53c2d3c3726f222a2' then 'Add'

when topics[0] = '0x88d38ed598fdd809c2bf01ee49cd24b7fdabf379a83d29567952b60324d58cef' then 'Remove'

end as actions

from

optimism.core.fact_event_logs a

left join optimism.core.fact_token_transfers b

on a.tx_hash = b.tx_hash

where