SELECT

address AS contract_address,

name AS token_name

FROM (

VALUES

('0x5430561b09c627264549fdb3a6154c34f5cabea7', 'btc'),

('0xb0ceff252f18710a3315e735b5e26481840ad286', 'usdc')

) AS T(address, name)

, ranked_transfers AS (

SELECT

open_trades.BLOCK_TIMESTAMP AS open_timestamp,

close_trades.BLOCK_TIMESTAMP AS close_timestamp,

open_trades.ORIGIN_FROM_ADDRESS AS trader_address,

open_trades.TO_ADDRESS AS token_perps_address,

CASE WHEN open_trades.contract_address = '0x4300000000000000000000000000000000000003'

THEN open_trades.RAW_AMOUNT/1e18 END AS open_amount,

CASE WHEN close_trades.contract_address = '0x4300000000000000000000000000000000000003'

THEN close_trades.RAW_AMOUNT/1e18 END AS close_amount,

open_trades.tx_hash as open_tx_hash,

close_trades.tx_hash as close_tx_hash,

ROW_NUMBER() OVER(

PARTITION BY open_trades.tx_hash

ORDER BY ABS(DATEDIFF(MINUTE, open_trades.BLOCK_TIMESTAMP, close_trades.BLOCK_TIMESTAMP))) AS row_num

FROM blast.core.fact_token_transfers AS open_trades

JOIN token_lookup AS t1

ON open_trades.TO_ADDRESS = t1.contract_address

AND open_trades.ORIGIN_TO_ADDRESS = t1.contract_address

JOIN blast.core.fact_token_transfers AS close_trades

ON open_trades.ORIGIN_FROM_ADDRESS = close_trades.ORIGIN_FROM_ADDRESS

JOIN token_lookup AS t2

ON close_trades.FROM_ADDRESS = t2.contract_address

AND close_trades.ORIGIN_TO_ADDRESS = t1.contract_address

WHERE