select tx_hash,

min (swap_index) min_index,

max(swap_index) max_index

from near.defi.ez_dex_swaps

where BLOCK_TIMESTAMP::date >= '2024-01-01'

and platform in ('v2.ref-finance.near','ref-finance.near')

group by 1

),

swapped_in as (

select

block_timestamp::date date ,

tx_hash,

trader ,

symbol_out token_in ,

amount_out amount_in ,

iff(zeroifnull(amount_out_usd) >1e6,0,amount_out_usd) AMOUNT_IN_USD

from near.defi.ez_dex_swaps left join min_max_swap_indexes using(tx_hash)

where BLOCK_TIMESTAMP::date >= '2024-01-01'

and platform in ('v2.ref-finance.near','ref-finance.near')

and swap_index = min_index

and min_index <> max_index

),

swapped_out as (

select

tx_hash,

symbol_in token_out ,

amount_in amount_out ,

zeroifnull(amount_in_usd) amount_out_usd

from near.defi.ez_dex_swaps left join min_max_swap_indexes using(tx_hash)

where BLOCK_TIMESTAMP::date >= '2024-01-01'

and platform in ('v2.ref-finance.near','ref-finance.near')

and swap_index = max_index

and min_index <> max_index

),

swap_in_one_index as (