SELECT

tx_id,

trader,

DATE_TRUNC('month', block_timestamp) AS swap_month,

amount_in_usd,

ROW_NUMBER() OVER (PARTITION BY trader ORDER BY block_timestamp) AS swap_rank

FROM

flow.defi.ez_dex_swaps

),

MonthlyUserActivity AS (

SELECT

swap_month,

trader,

CASE

WHEN swap_rank = 1 THEN 'new_user'

ELSE 'returning_user'

END AS user_type,

amount_in_usd

FROM

UserSwaps

SELECT

swap_month,

user_type,

COUNT(DISTINCT trader) AS user_count,

SUM(amount_in_usd) AS total_volume

FROM

MonthlyUserActivity

GROUP BY

swap_month, user_type

ORDER BY

swap_month, user_type;