SELECT

ORIGIN_FROM_ADDRESS AS user,

MIN(BLOCK_TIMESTAMP :: DATE) AS FIRST_SWAP_DATE

FROM base.defi.ez_dex_swaps

WHERE PLATFORM ILIKE '%aerodrome%'

GROUP BY 1

),

all_swaps AS (

SELECT

ORIGIN_FROM_ADDRESS AS user,

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day

FROM base.defi.ez_dex_swaps

WHERE PLATFORM ILIKE '%aerodrome%'

),

user_swap_analysis AS (

SELECT

asw.day,

asw.user,

CASE

WHEN asw.day = fs.first_swap_date THEN 'New Users'

ELSE 'Returning Users'

END AS user_type

FROM all_swaps asw

JOIN first_swap fs

ON asw.user = fs.user

SELECT

day,

user_type,

COUNT(DISTINCT user) AS user_count

FROM user_swap_analysis

GROUP BY 1, 2