-- Combine Spot and Perpetual traders

SELECT trader, block_timestamp

FROM arbitrum.vertex.ez_perp_trades

UNION ALL

SELECT trader, block_timestamp

FROM arbitrum.vertex.ez_spot_trades

),

first_trade_date AS (

-- Find the first trade date for each trader

SELECT

trader,

DATE_TRUNC('{{Granularity}}', MIN(block_timestamp)) AS first_trade_date

FROM data

GROUP BY trader

),

daily_traders AS (

-- Calculate daily new and returning traders

SELECT

DATE_TRUNC('{{Granularity}}', d.block_timestamp) AS date,

COUNT(DISTINCT d.trader) AS total_traders,

COUNT(DISTINCT CASE

WHEN f.first_trade_date = DATE_TRUNC('{{Granularity}}', d.block_timestamp) THEN d.trader

END) AS new_traders,

COUNT(DISTINCT CASE

WHEN f.first_trade_date < DATE_TRUNC('{{Granularity}}', d.block_timestamp) THEN d.trader

END) AS returning_traders

FROM data d

JOIN first_trade_date f

ON d.trader = f.trader

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

GROUP BY date

),

price AS (