swaps1 as (

SELECT

block_number,

event_index,

origin_from_address,

origin_to_address,

amount_in,

amount_in_usd,

token_in,

amount_out,

amount_out_usd,

token_out,

lag(origin_from_address,1) over (partition by block_number order by event_index) as origin_from_address2, -- partition by block_number order by event_index to because its the order in which txs occurred in a block

lag(origin_to_address,1) over (partition by block_number order by event_index) as origin_to_address2,

lag(amount_in,1) over (partition by block_number order by event_index) as amount_in2,

lag(amount_in_usd,1) over (partition by block_number order by event_index) as amount_in_usd2,

lag(token_in,1) over (partition by block_number order by event_index) as token_in2,

lag(amount_out,1) over (partition by block_number order by event_index) as amonut_out2,

lag(amount_out_usd,1) over (partition by block_number order by event_index) as amonut_out_usd2,

lag(token_out,1) over (partition by block_number order by event_index) as token_out2,

lag(origin_from_address,2) over (partition by block_number order by event_index) as origin_from_address3,

lag(origin_to_address,2) over (partition by block_number order by event_index) as origin_to_address3,

lag(amount_in,2) over (partition by block_number order by event_index) as amount_in3,

lag(amount_in_usd,2) over (partition by block_number order by event_index) as amount_in_usd3,

lag(token_in,2) over (partition by block_number order by event_index) as token_in3,

lag(amount_out,2) over (partition by block_number order by event_index) as amount_out3,

lag(amount_out_usd,2) over (partition by block_number order by event_index) as amount_out_usd3,

lag(token_out,2) over (partition by block_number order by event_index) as token_out3

from ethereum.core.ez_dex_swaps --where event_index between X and Y

),

swaps_filtered1 as (

SELECT * from swaps1

where

origin_from_address3=origin_from_address -- first buy wallets = last sell wallet

and token_in3=token_out -- first buy token = last sell token