SELECT

trader,

MIN(block_timestamp) AS initial_swap_time,

MAX(COALESCE(amount_out_usd, amount_in_usd)) AS near_received

FROM near.defi.ez_dex_swaps

WHERE symbol_in IN ('USDC', 'USDt', 'USDC.e', 'USDT.e', 'FRAX', 'DAI')

AND symbol_out = 'wNEAR'

AND DATE_TRUNC('day', block_timestamp) >= '2024-01-01'

AND COALESCE(amount_out_usd, amount_in_usd) BETWEEN 1000 AND 9999

GROUP BY trader

),

SUBSEQUENT_TRADES AS (

SELECT DISTINCT

t.tx_hash,

i.trader,

t.block_timestamp,

t.symbol_in,

t.symbol_out AS subsequent_token,

COALESCE(t.amount_out_usd, t.amount_in_usd, 0) AS subsequent_amount

FROM near.defi.ez_dex_swaps t

JOIN INITIAL_SWAPS i ON i.trader = t.trader

WHERE t.block_timestamp > i.initial_swap_time

AND t.symbol_in = 'wNEAR'

AND t.symbol_out NOT IN ('USDC', 'USDt', 'USDC.e', 'USDT.e', 'FRAX', 'DAI')

),

AGGREGATED_TRADES AS (

SELECT

subsequent_token,

COUNT(DISTINCT tx_hash) AS trade_count,

SUM(subsequent_amount) AS total_volume

FROM SUBSEQUENT_TRADES

GROUP BY subsequent_token

SELECT

subsequent_token AS "Subsequent Token",