-- List of all unique swappers and their first transaction dates

SELECT

origin_from_address,

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

FROM

kaia.defi.ez_dex_swaps

WHERE

platform = 'kaiaswap'

GROUP BY

origin_from_address

),

weekly_activity AS (

-- Weekly activity for all swappers

SELECT

DATE_TRUNC('week', block_timestamp) AS week,

origin_from_address

FROM

kaia.defi.ez_dex_swaps

WHERE

platform = 'kaiaswap'

),

new_and_returning AS (

SELECT

wa.week,

wa.origin_from_address,

CASE

WHEN wa.week = asw.first_week THEN 'new'

ELSE 'returning'

END AS swapper_type

FROM

weekly_activity wa

JOIN

all_swappers asw

ON wa.origin_from_address = asw.origin_from_address

),