-- Combine Spot and Perpetual trades and track the first trade date for each trader

SELECT trader, MIN(block_timestamp) AS first_trade_date

FROM (

SELECT trader, block_timestamp

FROM arbitrum.vertex.ez_perp_trades

UNION ALL

SELECT trader, block_timestamp

FROM arbitrum.vertex.ez_spot_trades

) trades

GROUP BY trader

),

trader AS (

-- Count distinct traders who have made a trade in the last '{{period}}' days

SELECT COUNT(DISTINCT trader) AS traders

FROM (

SELECT DISTINCT trader

FROM arbitrum.vertex.ez_perp_trades

WHERE block_timestamp >= current_date - interval '{{period}} days'

UNION ALL

SELECT DISTINCT trader

FROM arbitrum.vertex.ez_spot_trades

WHERE block_timestamp >= current_date - interval '{{period}} days'

) all_traders

),

new AS (

-- Count distinct traders whose first trade is within the '{{period}}' days

SELECT COUNT(DISTINCT trader) AS new_trader

FROM data

WHERE first_trade_date >= current_date - interval '{{period}} days'