-- Identify old traders (those who swapped before 2024)

SELECT DISTINCT trader

FROM near.defi.ez_dex_swaps

WHERE block_timestamp < '2024-01-01'

),

new_traders_swaps AS (

-- Identify swaps by new traders (first time in 2024)

SELECT

s.tx_hash,

s.trader,

s.amount_in_usd,

s.block_timestamp

FROM

near.defi.ez_dex_swaps s

WHERE

s.block_timestamp >= '2024-01-01'

AND s.block_timestamp <= '2024-12-31'

AND s.trader NOT IN (SELECT trader FROM previous_traders)

),

returning_traders_swaps AS (

-- Identify swaps by returning traders in 2024

SELECT

s.tx_hash,

s.trader,

s.amount_in_usd,

s.block_timestamp

FROM

near.defi.ez_dex_swaps s

WHERE

s.block_timestamp >= '2024-01-01'

AND s.block_timestamp <= '2024-12-31'

AND s.trader IN (SELECT trader FROM previous_traders)

-- New traders