-- Get the first time each trader appeared in the dataset

SELECT

TRADER,

MIN(DATE_TRUNC('{{Interval}}', block_timestamp)) AS first_trade_timestamp

FROM

near.defi.ez_dex_swaps

GROUP BY

TRADER

),

weekly_data AS (

-- Determine whether a trader is new or existing based on the current week

SELECT

first_trade_timestamp AS DATE,

count(DISTINCT TRADER) AS "New traders",

sum("New traders") over (order by Date) AS "Total Users"

from trader_first_activity

GROUP by 1

),

new_old_trader_counts AS (

-- Count the number of new and old traders per week

SELECT

DATE_TRUNC('{{Interval}}', block_timestamp) AS DATE,

count(DISTINCT TRADER) AS traders

FROM

near.defi.ez_dex_swaps where block_timestamp between '{{START_DATE}}' and '{{END_DATE}}'

group by 1

Date,

"New traders",

traders-"New traders" AS "Old Traders",

from weekly_data inner join new_old_trader_counts using (DATE)