SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS Date,

COUNT(DISTINCT SWAPPER) AS swappers

FROM aptos.defi.ez_dex_swaps

WHERE EVENT_ADDRESS='0x48271d39d0b05bd6efca2278f22277d6fcc375504f9839fd73f74ace240861af'

-- AND block_timestamp::DATE >= '2024-01-01'

GROUP by 1

),

tb2 AS (

SELECT

min(block_timestamp::date) as Min_date,

swapper AS new_swappers

FROM aptos.defi.ez_dex_swaps

WHERE EVENT_ADDRESS='0x48271d39d0b05bd6efca2278f22277d6fcc375504f9839fd73f74ace240861af'

-- AND block_timestamp::DATE >= '2024-01-01'

GROUP BY 2

),

tb3 AS (

SELECT

Min_date,

COUNT(DISTINCT new_swappers) as new_swappers_cnt

FROM tb2

GROUP BY 1

SELECT

Date,

Swappers-new_swappers_cnt AS "RETURNING SWAPPERS",

new_swappers_cnt AS "NEW SWAPPERS",

FROM tb1 a

JOIN tb3 b ON a.Date = b.Min_date