SELECT

DATE_TRUNC('week', block_timestamp) AS week,

buyer_address,

seller_address

FROM

sei.nft.ez_nft_sales

WHERE

platform_name = 'pallet'

),

cumulative_traders AS (

SELECT

week,

buyer_address,

seller_address,

ROW_NUMBER() OVER (PARTITION BY buyer_address ORDER BY week) AS buyer_first_seen_week,

ROW_NUMBER() OVER (PARTITION BY seller_address ORDER BY week) AS seller_first_seen_week

FROM

weekly_traders

),

weekly_summary AS (

SELECT

week,

COUNT(DISTINCT CASE WHEN buyer_first_seen_week = 1 THEN buyer_address END) AS new_buyers,

COUNT(DISTINCT CASE WHEN seller_first_seen_week = 1 THEN seller_address END) AS new_sellers,

COUNT(DISTINCT CASE WHEN buyer_first_seen_week > 1 THEN buyer_address END) AS returning_buyers,

COUNT(DISTINCT CASE WHEN seller_first_seen_week > 1 THEN seller_address END) AS returning_sellers,

(COUNT(DISTINCT CASE WHEN buyer_first_seen_week = 1 THEN buyer_address END) +

COUNT(DISTINCT CASE WHEN seller_first_seen_week = 1 THEN seller_address END)) AS total_new_traders,

(COUNT(DISTINCT CASE WHEN buyer_first_seen_week > 1 THEN buyer_address END) +

COUNT(DISTINCT CASE WHEN seller_first_seen_week > 1 THEN seller_address END)) AS total_returning_traders

FROM

cumulative_traders

GROUP BY