SELECT

origin_from_address AS user_id,

MIN(block_timestamp) AS first_tx_time,

COALESCE(amount_in_usd, amount_out_usd) AS initial_trade_size,

DATE_TRUNC('WEEK', first_tx_time) AS cohort_week,

CASE

WHEN initial_trade_size >= 10000 THEN 'Whale'

ELSE 'Normal'

END AS user_category

FROM avalanche.defi.ez_dex_swaps

WHERE platform IN ('pharaoh-v1','pharaoh-v2')

AND block_timestamp >= '2025-01-01'

GROUP BY origin_from_address, initial_trade_size

QUALIFY ROW_NUMBER() OVER (PARTITION BY origin_from_address ORDER BY first_tx_time) = 1

),

weekly_activity AS (

SELECT

DATE_TRUNC('WEEK', block_timestamp) AS event_week,

origin_from_address AS swapper_id,

COALESCE(amount_in_usd, amount_out_usd) AS trade_value

FROM avalanche.defi.ez_dex_swaps

WHERE platform IN ('pharaoh-v1','pharaoh-v2')

AND block_timestamp >= '2025-01-01'

SELECT

u.cohort_week AS "Cohort Week",

u.user_category AS "User Type",

COUNT(DISTINCT u.user_id) AS "New Users",

SUM(COUNT(DISTINCT u.user_id)) OVER (

PARTITION BY u.user_category

ORDER BY u.cohort_week

) AS "Cumulative Users",

COUNT(DISTINCT CASE