SELECT *,

CASE

WHEN origin_to_address = '0xb0be5d911e3bd4ee2a8706cf1fac8d767a550497' THEN 'bathETH'

WHEN origin_to_address = '0x7571cc9895d8e997853b1e0a1521ebd8481aa186' THEN 'bathWBTC'

WHEN origin_to_address = '0xe0e112e8f33d3f437d1f895cbb1a456836125952' THEN 'bathUSDC'

WHEN origin_to_address = '0x60daec2fc9d2e0de0577a5c708bcadba1458a833' THEN 'bathDAI'

WHEN origin_to_address = '0xffbd695bf246c514110f5dae3fa88b8c2f42c411' THEN 'bathUSDT'

WHEN origin_to_address = '0xeb5f29afaaa3f44eca8559c3e8173003060e919f' THEN 'bathSNX'

WHEN origin_to_address = '0x574a21fe5ea9666dbca804c9d69d8caf21d5322b' THEN 'bathOP'

END as pool_name

FROM optimism.core.fact_event_logs

WHERE origin_to_address IN ('0xb0be5d911e3bd4ee2a8706cf1fac8d767a550497', '0x7571cc9895d8e997853b1e0a1521ebd8481aa186',

'0xe0e112e8f33d3f437d1f895cbb1a456836125952', '0x60daec2fc9d2e0de0577a5c708bcadba1458a833',

'0xffbd695bf246c514110f5dae3fa88b8c2f42c411', '0xeb5f29afaaa3f44eca8559c3e8173003060e919f',

'0x574a21fe5ea9666dbca804c9d69d8caf21d5322b')

AND event_name IN ('Deposit', 'Withdraw')

AND block_timestamp > CURRENT_DATE - 30

), deposits as (

SELECT

a.block_timestamp,

date_trunc('HOUR', a.block_timestamp) as hour,

a.origin_from_address as user_wallet,

a.tx_hash,

a.event_name as action_name,

a.pool_name,

a.origin_to_address as pool_address,

b.contract_address as token_address,

a.event_inputs:value as amount_raw

FROM (

SELECT *

FROM all_events

WHERE event_name = 'Deposit'

) as a JOIN optimism.core.fact_event_logs as b

ON a.tx_hash = b.tx_hash AND a.origin_from_address = b.event_inputs:from

WHERE b.event_name = 'Transfer'