SELECT

origin_from_address,

MIN(DATE_TRUNC('week', block_timestamp)) AS first_trade_week,

COUNT(*) AS total_trades,

COUNT(DISTINCT DATE_TRUNC('week', block_timestamp)) AS active_weeks,

FROM arbitrum.defi.ez_dex_swaps

WHERE token_in=LOWER('0x13A7DeDb7169a17bE92B0E3C7C2315B46f4772B3')

OR token_out=LOWER('0x13A7DeDb7169a17bE92B0E3C7C2315B46f4772B3')

GROUP BY origin_from_address

),

Trader_Status AS (

SELECT

*,

CASE

WHEN active_weeks = 1 THEN 'New Trader'

ELSE 'Returning Trader'

END AS trader_status

FROM Weekly_Trader_Activity

first_trade_week,

trader_status,

COUNT(*) AS number_of_traders,

AVG(total_trades) AS average_trades_per_trader,

AVG(active_weeks) AS average_active_weeks

FROM Trader_Status

GROUP BY first_trade_week, trader_status

ORDER BY first_trade_week, trader_status;