SELECT

LOWER('0x' || SUBSTR(REGEXP_SUBSTR_ALL(SUBSTR(data, 3), '.{64}')[0], 25)) AS pool_address

FROM boba.core.fact_event_logs

WHERE

LOWER(contract_address) = LOWER('0xc35dadb65012ec5796536bd9864ed8773abc74c4') -- SushiSwap factory

AND topic_0 = '0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9' -- PairCreated

SELECT

tx_hash,

block_timestamp AS last_swap_time,

contract_address AS pool_address,

'0x' || SUBSTR(topic_2, 27) AS user_address

FROM boba.core.fact_event_logs

WHERE

topic_0 = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822' -- Swap

AND LOWER(contract_address) IN (SELECT pool_address FROM sushiswap_pairs)

ORDER BY block_timestamp DESC

LIMIT 100;