SELECT

tx_hash,

origin_from_address,

block_timestamp,

DATE_TRUNC('week', block_timestamp) AS swap_week

FROM

ink.core.ez_decoded_event_logs

WHERE

event_name = 'Swap'

AND origin_to_address = '0xa8c1c38ff57428e5c3a34e0899be5cb385476507'

AND block_timestamp IS NOT NULL

AND tx_succeeded = TRUE

),

FirstSwaps AS (

SELECT

origin_from_address,

MIN(swap_week) AS first_week

FROM SwapData

GROUP BY origin_from_address

),

WeeklyStats AS (

SELECT

s.swap_week,

s.tx_hash,

s.origin_from_address,

CASE

WHEN s.swap_week = f.first_week THEN 'New'

ELSE 'Returning'

END AS trader_type

FROM SwapData s

LEFT JOIN FirstSwaps f

ON s.origin_from_address = f.origin_from_address