SELECT

block_timestamp AS thor_date,

block_timestamp + INTERVAL '5 minute' AS thor_date_plus_5,

from_address AS thor_address,

tx_id AS thor_tx_id,

REGEXP_REPLACE(

REGEXP_SUBSTR(from_asset, '([^/\\.]+$)'),

'-.*',

''

) AS thor_from_asset,

REGEXP_REPLACE(

REGEXP_SUBSTR(to_asset, '([^/\\.]+$)'),

'-.*',

''

) AS thor_to_asset

FROM thorchain.defi.fact_swaps

WHERE block_timestamp >= '2023-01-01'

AND blockchain = 'ETH'

),

ethereum_swaps AS (

SELECT

block_timestamp AS eth_date,

block_timestamp + INTERVAL '5 minute' AS eth_date_plus_5,

tx_to AS eth_address,

symbol_in AS eth_symbol_in,

symbol_out AS eth_symbol_out

FROM ethereum.defi.ez_dex_swaps

WHERE block_timestamp >= '2023-01-01'

),

matched_swaps AS (

SELECT

t.thor_address AS arb_bot,

t.thor_tx_id,

CASE

WHEN t.thor_date < e.eth_date AND t.thor_to_asset = e.eth_symbol_in AND e.eth_date BETWEEN t.thor_date AND t.thor_date_plus_5 THEN t.thor_to_asset